DAX measure: MIN date for historical data in live connection

[Rsquare]

Hello all,

I have a live connection to a SQL table “table1” 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-16		2022-05-16

What to achieve:

Now, I would like to get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then.

Expected Result:

The output regarding the example table would be 2022-05-15. Solution: ID 4 and ID 5 are the only IDs that did not switch their state. ID 4 provides an earlier start date then ID 5.

I tried different approaches, like comparing a subset of Status A with a subset of other statuses to get the IDs that are in subset A but not in the other one. Another means could be an index if it would be possible to calculate a column.

However, since it is a live connection, I cannot calculate new tables or columns. The DAX measure has to be calculated without those intermediate steps. A change of the connection type is not possible.

I appreciate your help!

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Desired outcome measure: = 
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] )

[Rsquare]

Hi @Jihwan_Kim,

thanks for the solution! I got an additional request and need the ID/IDs according to this MIN value. Tried SELECTEDVALUE with FILTER regarding minx. But it does not work.   

Any help is appreciated, thanks!