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!
