Forum Discussion

Taffalaffa's avatar
Taffalaffa
Icon for Helper I rankHelper I
5 years ago

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!

LOOKUPVALUE
VLOOKUP

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    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]
    ),
    ""
    )
     
     
  • Taffalaffa's avatar
    Taffalaffa
    Icon for Helper I rankHelper I
    5 years ago

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