Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors