Power BI : Lookup Calculated Field

[Anonymous]

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.

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 :

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 

[ryan_mayu · Accepted solution]

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

[Anonymous]

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]

@Anonymous , Try a new column in table1

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

[Anonymous]

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.