topic Re: LOOKUPVALUE - Compare Text Values in columns to get a date value in DAX Commands and Tips

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

Taffalaffa — Tue, 29 Jun 2021 00:28:05 GMT

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!

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

Jihwan_Kim — Tue, 29 Jun 2021 02:39:13 GMT

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]

)

https://www.dropbox.com/s/ic374jdanlhxi9i/taffa.pbix?dl=0

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

Taffalaffa — Thu, 01 Jul 2021 15:43:10 GMT

@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).