cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Highlighted
Microsoft
Microsoft

Re: Ignore page & report level filters in a measure

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.
Highlighted
Helper II
Helper II

Re: Ignore page & report level filters in a measure

Have you ever solved this?

Highlighted
Helper I
Helper I

Re: Ignore page & report level filters in a measure

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors