topic Re: DAX measure: MIN date for historical data in live connection in DAX Commands and Tips

DAX measure: MIN date for historical data in live connection

Rsquare — Tue, 05 Jul 2022 08:59:59 GMT

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.

Re: DAX measure: MIN date for historical data in live connection

Jihwan_Kim — Tue, 05 Jul 2022 10:04:55 GMT

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

Re: DAX measure: MIN date for historical data in live connection

Rsquare — Tue, 25 Oct 2022 11:52:12 GMT

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!