Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
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.
Hi @usomaraju ,
Create a measure
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)
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.
Hi @usomaraju ,
Try this measure
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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
hi harsh,
And also i checked with selected the show items with no data , it also displaying the data from id 173.
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
User | Count |
---|---|
55 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
83 | |
54 | |
39 | |
21 | |
15 |