Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
This is a follow-up question from my last post: DAX measure: MIN date for historical data in live connection
I have a live connection to a SQL table “Data” with historical data.
Example Data:
ID | Status | Start | End | Changed |
1 | A | 2022-05-13 |
| 2022-05-13 |
1 | B | 2022-05-13 |
| 2022-05-14 |
2 | A | 2022-05-15 |
| 2022-05-15 |
3 | A | 2022-05-15 |
| 2022-05-15 |
2 | C | 2022-05-15 | 2022-05-16 | 2022-05-16 |
3 | B | 2022-05-15 |
| 2022-05-16 |
3 | B | 2022-05-15 |
| 2022-05-17 |
3 | C | 2022-05-15 | 2022-05-18 | 2022-05-18 |
4 | A | 2022-05-15 |
| 2022-05-15 |
4 | A | 2022-05-15 |
| 2022-05-16 |
5 | A | 2022-05-15 |
| 2022-05-16 |
6 | A | 2022-05-16 |
| 2022-05-16 |
I get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then according to the following formula (all thanks to Jihwan_Kim):
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
MINX ( _newtable, Data[Start] )
Result for example data: 2022-05-15
What to achieve:
Now, I would like to get the corresponding ID or IDs to the MIN Start-date respectively.
Expected Result:
ID 4
ID 5
I tried a combination of FILTER and SELECTEDVALUE but it did not work so far.
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
CALCULATE(SELECTEDVALUE(Data[ID]), FILTER(Data,Data[Start]=MINX( _newtable, Data[Start])))
Any suggestions?
Thanks!
Solved! Go to Solution.
Try
First ID =
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
MINX ( TOPN ( 1, _newtable, Data[Start], ASC ), Data[ID] )
Try
First ID =
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
MINX ( TOPN ( 1, _newtable, Data[Start], ASC ), Data[ID] )
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |