cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taffalaffa
Helper I
Helper I

LOOKUPVALUE - Compare Text Values in columns to get a date value

Hi... @Jihwan_Kim  @Fowmy @amitchandak @PaulOlding I am really hoping someone can help me.  I have a table in power BI.  Column A has the unique Project Number, Column B has an Activity ID. that is unique within the project but not unique otherwise; Column C has the actual start date of the activity in column B; Column D has the predecessor Activity ID (unique within the project but not unique otherwise).  What I am trying to do is compare Column D (Predecessor Activity ID) to Column B (Activity ID) within the same project (Column A) and return the Actual start date of the Predecessor Activity in Column E.... I cannot figure of the proper DAX expression and everytime I try it tells me it can't retun a date based on text columns. This is the result I am looking for but in PBI....  Any help would be really appreciated!

Results.PNG

2 REPLIES 2
Taffalaffa
Helper I
Helper I

@Jihwan_Kim thank you so much for helping me! @Jihwan_Kim @Fowmy @amitchandak @PaulOlding I am new to power BI and I really appreciate this.  I don't think I explained my issue well... I have one table that has all the information I need.  It has all the activities from all my projects.  It shows the predecessors to each activity.  What I need is to add a cloumn (Pred act_start_date) to fill in the predecessor actual finish date (if it has one).  I was hoping that within the same PROJ_ID, I could lookup the Predecessor_Task_Code value in the  the Task_Code column and then return the value of the TASK.act_start_date but I can't seem to figure out how to do it.  When I try the LookupValue option it says it can't return a date value based on text strings.   (Note that task code is unique within the PROJ_ID but can be resused amongst other PROJ_ID's.  Also every Predecessor_Task_Code exists in the TASK_CODE column).  My end goal is that I need to be able to compare the Predecessor Actual Start date (Pred act_start_date) to its Successor Actual Start Date (TASK.act_start-date).

Results.PNG

Jihwan_Kim
Super User
Super User

Picture1.png

 

Successor Activity ID : =
VAR currentproject =
MAX ( Projects[Project#] )
VAR currentrank =
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
VAR _rankbydate =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
Projects[Project#],
Activities[ActivityID]
),
"@rankbydate",
CALCULATE (
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
)
)
RETURN
COALESCE (
MAXX (
FILTER ( _rankbydate, [@rankbydate] = currentrank + 1 ),
Activities[ActivityID]
),
""
)
 
Successor Actual Startdate : =
VAR currentproject =
MAX ( Projects[Project#] )
VAR currentrank =
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
VAR _rankbydate =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
Projects[Project#],
Activities[ActivityID], Data[Actual Start]
),
"@rankbydate",
CALCULATE (
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
)
)
RETURN
COALESCE (
MAXX (
FILTER ( _rankbydate, [@rankbydate] = currentrank + 1 ),
Data[Actual Start]
),
""
)
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors