Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
For a Power BI report I'm trying to show the latest ParticipationDate per ID in a table visual, within the selected ParticipationDate range. Here is some sample data:
ID Partitipation date
1 02-03-2020
1 16-04-2020
1 02-12-2021
2 05-01-2020
2 08-09-2020
2 06-02-2021
The desired result, with the ParticipationDate range slicer set from 01-01-2020 to 31-12-2020:
ID PartitipationDate LatestParticipationDatePerID
1 02-03-2020 16-04-2020
1 16-04-2020 16-04-2020
2 05-01-2020 08-09-2020
2 08-09-2020 08-09-2020
I managed to show the latest date per ID with this calculated column:
LatestParticipationDatePerID = CALCULATE ( MAX ( Table1[ParticipationDate] ), ALLEXCEPT (Table1, Table1[ID] ) )
This shows the latest participation date grouped per ID, but still regardless of the selected date range. I can't seem to get the DAX to work when I add an ALLSELECTED of ParticipationDate.
How could the desired result be achieved?
Any suggestions would be much appreciated..
Thanks!
Solved! Go to Solution.
Hi @GKJARC ,
You could create date table as a slicer. Then create a measure by the following formula:
First: create Date table and Use as a slicer
Date = CALENDAR(MIN('Table'[PartitipationDate1]),TODAY())
Then create a measure
Latest =
MAXX (
FILTER (
ALL ( 'Table' ),
[ID] = MAX ( [ID] )
&& [PartitipationDate1] >= MIN ( 'Date'[Date] )
&& [PartitipationDate1] <= MAX ( 'Date'[Date] )
),
[PartitipationDate1]
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @GKJARC ,
You could create date table as a slicer. Then create a measure by the following formula:
First: create Date table and Use as a slicer
Date = CALENDAR(MIN('Table'[PartitipationDate1]),TODAY())
Then create a measure
Latest =
MAXX (
FILTER (
ALL ( 'Table' ),
[ID] = MAX ( [ID] )
&& [PartitipationDate1] >= MIN ( 'Date'[Date] )
&& [PartitipationDate1] <= MAX ( 'Date'[Date] )
),
[PartitipationDate1]
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very mucht @ v-yalanwu-msft for your reply. It is a solution to my initial request.
One follow-up question if I may. In this case the matching row should be marked as 1, the middle row in the example below.
My current calculated column 'Latest Y/N' ignores the selection Date[Date], is there a way to include this Date selection in the calculated column?
Latest Y/N =
IF ( [Latest participation date within selection] = Table1[ParticipationDate], 1, 0)
@GKJARC , Try like
CALCULATE ( MAX ( Table1[ParticipationDate] ), filter(allselected(Table1), Table1[ID] =max(Table1[ID]) ) )
Thanks @amitchandak for your reply. After creating this calculated column, for some reason it still seems to select the max ParticipationDate outside of the selected ParticipationDate range. The date range slicer is also Table1[ParticipationDate] so I don't understand why ALLSELECTED doesn't seem to work in this case.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |