Delinking Page Filters on few Visuals

I have used a Page Filter to apply only one type of value across all visuals but want to delink the same Page Filter on one Table visual.

How can I achieve this outcome?

@brjeeth,

Welcome to the forum – great to have you here!

You can do this through the application of the ALL function in DAX.

In this example, the top table has the page filter limiting it to Products 1-4 applied.

The second table uses this measure to remove the filter from the Product Name field (the one filtered at the page level):

Override Page Filter = 
CALCULATE(
    [Total Sales],
    ALL( Products[Product Name] )
)

I hope this is helpful. Full solution file attached below.
This video does a great job of explaining the ALL function in DAX:

  • Brian

eDNA Forum – Override Page Filter solution.pbix (515.3 KB)

2 Likes

@BrianJ: Thanks for the quick response…I have been getting lot of value from both membership and solutions listed in Forum…so I couldn’t be happier!!

I should have give more info regarding my problem…I work in Television industry and have 3 broad categories of advertisements (Commercial, Trailer, Promo). I want all my Visuals on the page to be run just on “Commercial” type so I have applied the Page Filter. However, I want the Advertisement visual with all 3 categories.

Using the example you provided, if you can show how to accomplish using the Product Name column instead of Year, that will solve my problem.

Thanks a bunch!!

@brjeeth,

Sure thing. To make the example more specific to your use case I created an Ad Type column in the Sales table, and then put a page filter on selecting Commercial only. To override, it uses the same ALL concept as above, this time just removing the filter on Sales[Ad Type].

Here’s the measure used this time:

Override Page Filter Ad Type = 
CALCULATE(
    [Total Sales],
    ALL( Sales[Ad Type] )
)

and here’s what it looks like in the visuals:

I hope this gets you what you need. If not, just give a shout.

Full revised solution file attached .

I am trying the use the same column in the Table visual as well as the Filter. In this case, you have used Ad Type in the filter and Product Name in the Table.

See the screenshots below…hopefully this explains the problem better. I want to see all Adv Types (similar to Table 1) when I have the Page filter setup as Adv Type = Commercial

Page Filter “Adv Type = Select All” (Table 1)

Page Filter “Adv Type = Commercial” (Table 2)

DAX Formula:

image

image

I’m sure you figured the problem but here are the similar screenshots from the pbix file you attached in previous post:

AdType = Select all (Table 1)

AdType = Commercial (Table 2)

I want to see all 3 rows in the Table 2 even when ‘AdType = Commercial’

@brjeeth,

Okay, thanks for the clarification. Hopefully the third time’s a charm. :grinning:

By filtering on the field that you are using as your row value, you’ve substantially increased the complexity of the solution. This is fine – as you can see from the screenshot below, it’s totally doable, but will require significantly more explanation:

image

Case 1 is not what you want, but I included for comparison and clarity’s sake. This is simply the ad type column and the most basic total sales measure. As you can see, it gives the right value for commercial sales, but filters the rows down to only commercial, which is not what you want.

In order to keep a page filter or a slicer from filtering the row values, you need to create a disconnected (i.e., no relationship in the data model) table of those values. We can do this in Power Query, which would be optimal, but for the sake of speed and clarity I created it via DAX using the Modeling-New Table command on the ribbon and entered the following DAX to actually create the one column table of unique ad type values:

Disconnected Ad Type = 
VALUES( Sales[Ad Type] )

Now in our tables 2 and 3, we can use the Ad Type column from this disconnected table as our row value and it won’t be filtered by the page filter. Problem solved, right?

Not quite. Because if we drop our measures into that table now, because that column comes from a disconnected table not linked to our data model, the measures as is will not provide correct values. In order to get them to calculate properly, we need to create a virtual relationship between our disconnected table and our sales table. We do that using a function called TREATAS. I think this is one of the more difficult DAX functions to understand, but once you do it is a phenomenally powerful function that you’ll end up using it in all sorts of ways. This is the best explanation of TREATAS I’ve ever seen (do yourself a favor and DON’T read the Microsoft documentation on this function – it’s absolute gibberish that I still don’t understand after reading it over and over):

Table 3 is now just a revised version of our old friend the ALL variant, with the new TREATAS component added to create the needed virtual relationship:

Override Page Filter Ad Type = 
CALCULATE(
    [Total Sales],
    ALL( Sales[Ad Type] ),
    TREATAS(
        VALUES( 'Disconnected Ad Type'[Ad Type] ),
        Sales[Ad Type]
))

Table 2 requires even a bit more advanced construct using SWITCH(TRUE() ) to step through the various page filter selection possibilities. (Note: to simplify(?!) things, I’ve set the page filter to “single selection only”, allowing for either no selection in the page filter (meaning all types) or selection of just a single type.) Here’s the measure:

Selected Total Sales = 
CALCULATE(
    SWITCH( TRUE(),
        [Harvest Ad Type] = "None Selected", [Total Sales],
        SELECTEDVALUE( Sales[Ad Type] ) = [Harvest Ad Type], [Total Sales],
        BLANK()
    ),
    VALUES( Sales[Ad Type] ),
    TREATAS(
        VALUES( 'Disconnected Ad Type'[Ad Type] ),
        Sales[Ad Type]
    )
)

Basically, what this is doing is stepping through the various page filter possibilities. If there is no selection, it is using [Total Sales] for each ad type, with the TREATAS virtual relationship. If a single type is selected, it is using [Total Sales] for that type, and setting the other types to blank, again via TREATAS. To get the blank rows to show, make sure you have this option checked in your visual next to the ad type field in table 2:

image

If you’re not already familiar with it, here’s a great explanation of the SWITCH(TRUE()) construct, which IMO is one of the best tools in the DAX toolbox:

This is probably a lot more than you bargained for when you asked the question, but hopefully is helpful. Full revised solution file posted below.

2 Likes

@BrianJ: Let me start by saying “You are AWESOME!!” :clap: :clap: :clap:

Just reading the steps is making my head spin but it does make sense. I’m sure I’ll be spending my entire Monday to replicate what you have done in few hours…Thanks a lot!!

Will let you know the outcome (hopefully by end of tomorrow)

Ranjith

@brjeeth,

Thanks – glad to help. With a seemingly minor change in requirements, you waded into some pretty deep waters, but if you methodically work through how to use ALL/ALLEXCEPT/ALLSELECTED to retain/remove filters as needed, TREATAS to create virtual relationships and SWITCH(TRUE()) to manage groups of conditional statements, you will have some incredibly powerful tools at your disposal that can be used in a nearly infinite number of ways.

Good luck, and just give a shout if you run into any problems.

  • Brian

P.S. as soon as you have a chance, I would strongly recommend working your way through the following course start to finish. @sam.mckay recently completely overhauled it, and the course does a fantastic job not only explaining DAX best practices and core functions, but how the functions work together to address common applied scenarios. All the approaches I used in the solution above are beautifully explained in this course:

Hi @brjeeth, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

The solution worked like a charm…Thanks!!