cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors