Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rsquare
Frequent Visitor

DAX measure: ID from MIN date - historical data in live connection

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!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors