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
Anonymous
Not applicable

Power BI : Lookup Calculated Field

Hi, I need to create a calculated column (not a measure) which first filters the table based on few conditions and then give me the column value corresponding to the row which has survived all the filters. Below is my problem statement.

 

I have two input tables:

1. Document Upload : A fact table which stores records of any time a Document is being uploaded for a case.

2. App History : Also a fact table which stores history of when a case moves from one stage to another.

 Below are the sample of both the tables.

 

DocInput.PNG

 

What I need is that, a calculated column to be derived which tells me what was the stage/status of the case when the document was being uploaded irrespective of its current stage. Expected output is below :

DocOutput.PNG

 

So far, with the help of below DAX, I am able to derive the Max DateTime in App History table which is less than the Document Upload Time. I want the "New Stage" column value corresponding to that date.

 

Nearest Date= CALCULATE(MAX(AppID_History[Modified_On]), FILTER(AppID_History, AppID_History[AppIDId] = EARLIER(DocumentUpload[AppID])), FILTER(AppID_History, AppID_History[Date] <= DocumentUtility[Document Upload Date]))

 

Please help me with that DAX. If no date less than the document upload dateTime is found than I want a default value as "Stage 1".
@amitchandak @GilbertQ 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Anonymous 

maybe you can try this

Column = 
VAR _date=MINX(FILTER('table2',table2[Date]>=table1[Document upload Date]&&table1[APPID]=table2[APPID]),'table2'[Date])

return MAXX(FILTER('table2','table1'[APPID]='table2'[APPID]&&'table2'[Date]=_date),'table2'[Previous Stage])

1.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

@Anonymous 

maybe you can try this

Column = 
VAR _date=MINX(FILTER('table2',table2[Date]>=table1[Document upload Date]&&table1[APPID]=table2[APPID]),'table2'[Date])

return MAXX(FILTER('table2','table1'[APPID]='table2'[APPID]&&'table2'[Date]=_date),'table2'[Previous Stage])

1.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks @ryan_mayu just a small tweak in your DAX is giving me the desired results. Instead of MINX, it will be MAXX to derive _date variable. Since I need max date which is less than document upload date. Thank you 🙂

amitchandak
Super User
Super User

@Anonymous , Try a new column in table1

maxx(filter(Table2, Table2[Date] >=Table1[date] && Table1[AppID] = Table2[AppID]), firstnonblankvalue(Table2[Date], Table2[Stage]))

Anonymous
Not applicable

Thank you @amitchandak for the quick reply, but somehow your DAX is giving me the status corresponding to the 2nd highest date instead of MAX date. Not sure why but your logic also seemed correct. 

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.