Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Solved! Go to 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])
Proud to be a 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])
Proud to be a Super User!
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 🙂
@Anonymous , Try a new column in table1
maxx(filter(Table2, Table2[Date] >=Table1[date] && Table1[AppID] = Table2[AppID]), firstnonblankvalue(Table2[Date], Table2[Stage]))
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |