Based on distinct of id, get the earliest date from the next column

[usomaraju]

Hi,

Could someone help me on below issue.

i have the table with 3 columns

id	enddate	no of days
200	3/13/2020	0
200	3/13/2020	0
202	3/19/2020	15
202	3/19/2020	15
202	3/27/2020	15
207	5/1/2020	6
207	5/6/2020	6

Out of which i need distinct of id with earliest of the date and corresponding days i need to store in a separate table or do filter on the same table.Is it possible to do? 

and the output for the above looks like below

id	enddate	no of days
200	3/13/2020	0
202	3/27/2020	15
207	5/6/2020	6

[Mariusz]

Hi @usomaraju 

Try this.

Measure = 
SUMX(
    SUMMARIZE( 'Table', 'Table'[id], 'Table'[enddate] ),
    VAR __minDate = 
        CALCULATE(
            MIN( 'Table'[enddate] ), 
            ALLEXCEPT( 'Table', 'Table'[id] )  
        ) 
    RETURN 
        IF( 
            __minDate = 'Table'[enddate],
            CALCULATE( 
                MIN( 'Table'[no of days] ),
                'Table'[enddate] = __minDate 
            )
        )
)

for the below result 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[usomaraju]

Hi Mariusz,

I tried your DAX formula, which returns only few records and also some of the id's of rows are missing

like for some of the rows below

id	enddate	no of days
163	2/25/2020	2
163	2/25/2020	2
163	2/28/2020	2
164	2/25/2020	1
164	2/25/2020	1
165	3/2/2020	6
165	3/2/202	6

when i apply measure, i get only one row as reslut for the id 164

id	enddate	no of days	Measure
164	2/25/2020	1	1

i'm not sure why its not populating the result for 163 and a65.

[harshnathani]

Hi @usomaraju ,

Create a measure

Max Date Id = CALCULATE(MAX('Table'[enddate]), ALLEXCEPT('Table','Table'[id]))

Don't Summarize when you pull number of Days.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[usomaraju]

Hi Harsh,

when i applied dax formula on my table, i see below result and every row it is calculating and i'm not sure about the valuse displaying in the measure column

for ex: for the id 163, i need the result as one row and the date should be 2/28/2020.

[harshnathani]

Hi @usomaraju ,

Try this measure

Max Date Id =

var a = MAXX(FILTER(ALL('Table'), 'Table'[ID] = Max('Table'[ID])), 'Table'[Date])

var b = If(MAx('Table'[Date]) = a,a ,Blank())

Return

b

Select Show items with no data

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[usomaraju]

hi Harsh

Thank you so much for your quick response

i have used your dax, but in the result it starts from the id 173and not showing previous id's

i'm not sure why

before measure

with measure

[usomaraju]

hi harsh,

And also i checked with selected the show items with no data , it also displaying the data from id 173.

[harshnathani]

Hi @usomaraju ,

Not sure why is this happening. It shows all values in my pbix.

Can you share sample of your pbix file for me to check.

Regards,

Harsh Nathani