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.
Is it possible to ignore page & report level filters in a measure? I have the following Page level filters:
Now, I'm trying to get the distinct count of reports using [Report no.] like so =
Reports = DISTINCTCOUNT([Report no.])
If I apply that on the page that has the filters above, it will get the distinct count of the reports with the given filters, right? Now, what if I wanted to get the total distinct count of reports that are SI/SI-A? Ie:
- Status = Completed
- Period = All periods (ie: YTD)
- Result = All results
- Report no = Not blank
- SI/UL/SI-A = SI or SI-A only
In a nutshell, I'll be ignoring the following page level filters in the measure:
- Period
- Result
I tried using ALLEXCEPT like this:
CALCULATE(DISTINCTCOUNT(DS[Report no.]), ALLEXCEPT(DS, DS[SI/UL/SI-A], DS[Period], DS[Status (Completed / Pending)]))
But I'm not really getting the right numbers (way off, I'm only getting single digit numbers, where it should be 670). Anyone have any clues to this? Also, how does ALLEXCEPT work exactly? I tried using DAX Studio to see (as in literally see, as I'm more of a visual learner) how it works, but I'm not sure how to use it there either.
@olimilo, have you tried using the all filter? So, in your measure, starting with a clean slate and filtering out what you don't want.
So, Reports = Calculate(DISTINCTCOUNT([Report no.]), FILTER(ALL(DS))) would give you the distinct count of reports, ignoring any page level or report level filters.
So, to ignore your page level Period and Result filters, start will all your data and filter Status = Completed, Report no = Not blank, and SI/UL/SI-A = SI or SI-A only.
Reports = Calculate(DISTINCTCOUNT([Report no.]), FILTER(ALL(DS), DS[Status (Completed / Pending)] = "Complete" && DS[Report no.]>0 && (DS[SI/UL/SI-A]="SI" || DS[SI/UL/SI-A]="SI-A")))
Edit: I think you were on the right track with ALLEXCEPT. The columns in the ALLEXCEPT filter should only be those whose page/report level filters your want to keep -in your case, Status, Report No., and SI/UL/SI-A. Try this: CALCULATE(DISTINCTCOUNT(DS[Report no.]), ALLEXCEPT(DS, DS[Status (Completed / Pending)], DS[Report no.], DS[SI/UL/SI-A]))
Have you ever solved this?
Hi @olimilo,
In your scenario, there is no method to ignore page or report level filters for Initial Inspection Result field and Period field.
Reserve your original DAX formula to create Reports measure, remove Initial Inspection Result field and Period field from page or report level filters, then create two slicers using Initial Inspection Result field and Period field. Click each slicer and select “Edit interactions” under Format ribbon, then select “None” for the visuals that you use Reports measure to create, this way, when you select values in page/report level filters and slicers, you will get your expected result.
Thanks,
Lydia Zhang
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |