Get the distinct values from few columns based on the ID

[usomaraju]

Hi, 

Could someone help me on getting the dax formula 

Here is my table data

id	name	startdate	enddate	no of days	stages
200	abc	3/13/2020	3/13/2020	0	stg1
200	abc	3/13/2020	3/13/2020	0	stg2
202	xyz	3/18/2020	3/19/2020	15	stg2
202	xyz	3/13/2020	3/19/2020	15	stg1
202	xyz	3/12/2020	3/27/2020	15	stg3
207	123	4/30/2020	5/1/2020	6	stg1
207	123	4/30/2020	5/6/2020	6	stg2

the no of days is calculated by the difference between the startdate for the ID and the end date for the same id 

eg: for 202, the startdate is 3/12/2020 and enddate is 3/27/2020 .So the difference is 15 days.

and i need the result like below based on id

200	abc	3/13/2020	3/13/2020	0	stg2
202	xyz	3/12/2020	3/27/2020	15	stg3
207	123	4/30/2020	5/6/2020	6	stg2

once i get the above table, i need to merge this result with the datetable provided by the powerbi , can someone please provide suggestions, how can we do to get the monthly report based on calender days from the datetable not based on the dates available in the existing table.

[JustJan]

Hi @usomaraju,

The first part of your question is pretty straightforward, most of the measures can be created via the Quick Measure Dialog:

Duration	=[Last enddate max per id] - [First startdate min per id]
First startdate min per id	= MINX (     KEEPFILTERS ( VALUES ( 'data'[id] ) ),     CALCULATE ( MIN ( 'data'[startdate] ) ) )
Last enddate max per id	= MAXX (     KEEPFILTERS ( VALUES ( 'data'[id] ) ),     CALCULATE ( MAX ( 'data'[enddate] ) ) )
Last stage per id	= MAXX (     KEEPFILTERS ( VALUES ( 'data'[id] ) ),     CALCULATE ( MAX ( 'data'[stages] ) ) )

Not sure what you mean exactly with the month report based on calendar days.

- Jan

[usomaraju]

hi jan , i have tried the quick measure for to get the max completed date based on the ID, which is not providing the values correctly.

see here the Quick measure columne displaying the same results as completed column.

i need for each id, one row with mx completed date which is for id 163, the valuse is 2/28/2020.

same for 166, it is 3/3/2020.

[JustJan · Accepted solution]

Hi @usomaraju, 

Providing the measure is setup correctly, the reason that you don't see the expected values is because the table contains data columns that are not the same for an ID.

What if you create a table with just ID and the measure, then you should get one line per id with the max completed date for the ID.

If you now add you column 'startdate', which has multiple values per id, you will that you will get multiple line per ID. That is why use a measure to show the earliest (min) startdate per id instead of the actual column.

Hope this helps

-Jan