Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
olimilo
Responsive Resident
Responsive Resident

Ignore page & report level filters in a measure

Is it possible to ignore page & report level filters in a measure? I have the following Page level filters:

 

2017-04-20 19_09_34-Pathfinder CHN-GM - Power BI Desktop.png

 

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.

3 REPLIES 3
megm001
Advocate I
Advocate I

@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 = CompletedReport 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]))

 

Silko
Helper II
Helper II

Have you ever solved this?

v-yuezhe-msft
Employee
Employee

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.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.