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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.