topic Re: Based on distinct of id, get the earliest date from the next column in DAX Commands and Tips

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

usomaraju — Fri, 19 Jun 2020 03:21:45 GMT

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

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

Anonymous — Fri, 19 Jun 2020 07:34:36 GMT

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)

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

Mariusz — Fri, 19 Jun 2020 14:39:03 GMT

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

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

usomaraju — Sun, 21 Jun 2020 16:59:05 GMT

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.

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

usomaraju — Sun, 21 Jun 2020 17:21:54 GMT

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.

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

Anonymous — Sun, 21 Jun 2020 17:56:51 GMT

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

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)

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

usomaraju — Sun, 21 Jun 2020 21:45:08 GMT

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

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

usomaraju — Sun, 21 Jun 2020 21:52:35 GMT

hi harsh,

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

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

Anonymous — Mon, 22 Jun 2020 04:38:32 GMT

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