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 there,
Im trying to create a measure that would show the DistinctCount of results based on the filters selected by the user.
There are 3 slicers:
- Year;
-Month;
-Week;
The Goal is to show the total DistinctCount of a collumn, if the value is within the selected range, for example:
If the user selects 2022, the output would be the total distincts of 2022, but if the user then selected on another filter the month March, the output would be the total distincts for 2022 - March, and same for Weeks.
Im also trying to do the same but with results that happen AFTER the selected period. So if the user would select 2022, It would display results for 2023, if the user selected March, It would show results for 2023 and after March.
However, if the user deselected year, the results would be filtered according to the other selected filters, ex: All values for March and All values after March.
Thanks in advance!
Solved! Go to Solution.
Here is one solution. Create these two measures:
DistinctWaveIDs = DISTINCTCOUNT('Waves'[Wave ID])
DistinctWaveIDs (Upcoming) =
VAR _LastDate = LASTDATE('Calendar'[Date])
RETURN
CALCULATE(
[DistinctWaveIDs]
, 'Calendar'[Date] > _LastDate
)
Use this data model:
Use this set of test data:
Start Date | . | WaveID |
01/01/2022 | 1 | |
01/03/2022 | 2 | |
31/03/2022 | 3 | |
01/04/2022 | 4 | |
16/04/2022 | 5 | |
25/12/2022 | 6 | |
01/01/2023 | 7 | |
15/01/2023 | 8 | |
02/03/2023 | 9 | |
03/03/2023 | 10 | |
03/03/2023 | 11 | |
04/03/2023 | 11 |
I have two slicers, one for Year, the other for YearAndMonth:
Here are the results:
When I select Year 2022, I correctly get 6 DistinctWaveIDs and 5 for Upcoming (remember that WaveID 11 appears twice in my test data so should only count as 1 in the DistinctCount).
When I select 2022 and March (202203) I correctly get 2 and 8 upcoming.
Hope this helps.
Hi @Anonymous
You can drag your date column into a slicer, then edit your slicer to use the select a dates in a range or after a specific date, you can see more here https://docs.microsoft.com/en-us/power-bi/create-reports/slicer-filter-relative-time?tabs=powerbi-desktop
Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated
I can't add more slicers to the page, and the values are to be displayed in a table alongside other values which can't be filtered the same way this collum will.
I tried this metric without success:
Here is one solution. Create these two measures:
DistinctWaveIDs = DISTINCTCOUNT('Waves'[Wave ID])
DistinctWaveIDs (Upcoming) =
VAR _LastDate = LASTDATE('Calendar'[Date])
RETURN
CALCULATE(
[DistinctWaveIDs]
, 'Calendar'[Date] > _LastDate
)
Use this data model:
Use this set of test data:
Start Date | . | WaveID |
01/01/2022 | 1 | |
01/03/2022 | 2 | |
31/03/2022 | 3 | |
01/04/2022 | 4 | |
16/04/2022 | 5 | |
25/12/2022 | 6 | |
01/01/2023 | 7 | |
15/01/2023 | 8 | |
02/03/2023 | 9 | |
03/03/2023 | 10 | |
03/03/2023 | 11 | |
04/03/2023 | 11 |
I have two slicers, one for Year, the other for YearAndMonth:
Here are the results:
When I select Year 2022, I correctly get 6 DistinctWaveIDs and 5 for Upcoming (remember that WaveID 11 appears twice in my test data so should only count as 1 in the DistinctCount).
When I select 2022 and March (202203) I correctly get 2 and 8 upcoming.
Hope this helps.
Thank you! That worked very well! 🙂
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |