Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
32 | |
27 | |
24 | |
22 |