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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors