Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I'm building a report to provide the summary of the month in counts of various items. I have 2 slicers to select Year and Month, as well as an visualization filter on Incident Type to display only the top 5 counts.
Now, I'm trying to calculate the YTD Count. I figured I needed to do a count, where I remove the context of the Month slicer and the Incident Type filter. I came up with the following:
Incident YTD Count = CALCULATE(
DISTINCTCOUNT('Incident Information'[Incident Number]),
all('Incident Information'[Incident Type]),
ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]))
This properly calculates the total for the year (it correctly removed the month slicer and the top 5 filters). Now, I needed to add a filter to only take the months before the selected one. So I added the following:
Incident YTD Count = CALCULATE(
DISTINCTCOUNT('Incident Information'[Incident Number]),
all('Incident Information'[Incident Type]),
ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]),
FILTER('Incident Start Date','Incident Start Date'[Month Number]<=[Selected Month]))
However, this just gives me the total of the selected month. It is as if the Filter still have the context for the month slicer.
What am I missing?
Hi @EricPierre,
Please share some sample data and clarify desired results. It would be better if you can share sample pbix file.
Best Regards,
Qiuyun Yu
Hello Qiuyun Yu,
Currently, in the table, I have Total of incident in selected month and % per type. These work perfectly.
Now, I would like to add a column showing the YTD count per incident. As mentioned in my original post, I am able to remove the filter by incident type (Top 2) and keep the filter for Year. However, when I try to add a filter for Date smaller than selected, it appears the month selection set in the slicer still applies.
In numbers, for February 2016, I have 237 incidents. (1 doesn't show up as it is not in the top 2, % indicates that by not adding to 100%).
The following gives me 237 (as if last line has a context of only the data found using the slicer):
Incident YTD Count = CALCULATE(
DISTINCTCOUNT('Incident Measures'[Incident Number]),
ALL('Incident Information'[Incident Type]),
ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]),
FILTER('Incident Start Date','Incident Start Date'[Date]<=[Selected Month]))
thanks,
EricPierre
Furthermore, the following gives me the total for the full year (as I would have expected), which is 3386:
Incident Year Count = CALCULATE(
DISTINCTCOUNT('Incident Measures'[Incident Number]),
ALL('Incident Information'[Incident Type]),
ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]))
My idea was that if I add a filter stating that I wanted only the months before the selected month, that would do the trick. But when I add a filter for month, then the slicer selection seems to apply, and I only get the selected month total.
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |