Keeping context from within a visual while ignoring filters from elsewhere.

[Connor888]

The source of this issue is a field parameter. I have a visual (just a table) with a measure shown, and optional fields which break down the measure selected by a field parameter. As a simplified example, I have data something like this. The first table, Customer:

Customer Region	Customer Segment	Customer Name
Europe	Industrial	Foo
Europe	Industrial	Bar
Americas	Tech	Baz

And meanwhile Product:

Product Number	Product Category
A	Hats
B	Shoes
C	Coats

These are joined to a sales table which holds the actual numerical data. My filter parameter has all the fields from both tables, so I can look at sales at any level or combination of levels. I also have slicers on this page which filter things down based on other attributes not in the filter parameter. So my sales measure respects all that context - the visual and the slicers - but I want to have alongside it a total which would calculate the sales respecting only the context from within the visual (that is, only filtering on the fields selected in the field parameter - if for example only Product Number is selected, I want to see all sales for the product regardless of  any other slicer selections).

This should be as simple as using REMOVEFILTERS, and including all the fields in the slicers, right? Except some of my slicers use filters that appear in the field parameter - so either they are removed, and the filtering breaks when those fields are selected, or they aren't removed and it breaks the rest of the time! The next option would be ALLEXCEPT - but this can't accept the field parameter as its second argument, indeed it seems that ALLEXCEPT can only use a static list of field references rather than a dynamic one.

So, what would be a very straightforward problem ordinarily becomes seemingly impossible with the field parameter. Hopefully, though, there's something I've missed which someone can enlighten me on! 🙂

[Connor888]

Hi @OwenAuger ,

Ah, marvellous! The first solution is simple and works perfectly. Many thanks!!

Connor

[OwenAuger]

Hi @Connor888 

This sounds like an interesting problem 🙂

I think I've mostly understood the requirement for the "Total" measure: it should calculate Sales for the current "row" of the visual, but ignoring filters from slicers outside the visual (let me know if that's not right).

But just to be sure, could you post an example (using dummy data if necessary) showing the value you would expect to see for this "Total" measure in a simple scenario?

If you can provide a link to a PBIX that would be useful too.

Regards

[Connor888]

Hi @OwenAuger ! Thanks for your reply!

Yes, you have it exactly right. If I imagine a portion of a sales table, like so:

Product Number	Customer Name	Units Sold
A	Foo	10
A	Bar	5

This would be joined to the product and customer tables. If we setup a table visual with the analysis fields, we might produce a table visual like this:

Product Number	Customer Segment	Units Sold
A	Industrial	15

But with a slicer filtering on Customer Name = "Foo" then Units Sold would drop to 10. I would like a measure that shows 15 in all circumstances.

[OwenAuger · Accepted solution]

Hi again @Connor888 

Thanks for clarifying! 🙂

After mulling over this one, I have come up with a couple of possible methods. PBIX attached.

1. Method 1 (my preferred method):
Create copies of all dimension tables that will be used to create filters outside the visual itself.

1. Each copy of a dimension table should have an active relationship to the fact table.
2. Use the copy of the dimension table on all slicers.
3. In the sample PBIX, I called these 'Customer Filter' and 'Product Filter'.
4. Write the following measure:

Units Sold Total Method 1 = 
CALCULATE (
    [Units Sold Measure],
    REMOVEFILTERS ( 'Customer Filter' ),
    REMOVEFILTERS ( 'Product Filter' )
)

2. Method 2a:

Total measure checks if each possible column is selected within Field Parameter

1. If a particular column is selected in field parameter, then apply an unchanged filter equal to the value of that column (or values at a total level).
2. Requires enumerating each possible column in the measure, so could be tedious to maintain.

Units Sold Total Method 2a = 
VAR FieldParameterSelection =
    SELECTCOLUMNS (
        'Customer/Product',
        "@FieldParameter", 'Customer/Product'[Customer/Product]
    )
VAR Filter_CustomerName =
    FILTER (
        ALL ( Customer[Customer Name] ),
        NOT "Customer Name" IN FieldParameterSelection
            || Customer[Customer Name] IN VALUES ( Customer[Customer Name] )
    )
VAR Filter_CustomerRegion =
    FILTER (
        ALL ( Customer[Customer Region] ),
        NOT "Customer Region" IN FieldParameterSelection
            || Customer[Customer Region] IN VALUES ( Customer[Customer Region] )
    )
VAR Filter_CustomerSegment =
    FILTER (
        ALL ( Customer[Customer Segment] ),
        NOT "Customer Segment" IN FieldParameterSelection
            || Customer[Customer Segment] IN VALUES ( Customer[Customer Segment] )
    )
VAR Filter_ProductCategory =
    FILTER (
        ALL ( Product[Product Category] ),
        NOT "Product Category" IN FieldParameterSelection
            || Product[Product Category] IN VALUES ( Product[Product Category] )
    )
VAR Filter_ProductNumber =
    FILTER (
        ALL ( Product[Product Number] ),
        NOT "Product Number" IN FieldParameterSelection
            || Product[Product Number] IN VALUES ( Product[Product Number] )
    )
RETURN
    CALCULATE (
        [Units Sold Measure],
        Filter_CustomerName,
        Filter_CustomerRegion,
        Filter_CustomerSegment,
        Filter_ProductCategory,
        Filter_ProductNumber
    )

2. Method 2b:

Total measure checks if each possible column is INSCOPE

1. If a particular column is INSCOPE, then it must have been in the field parameter selection.
2. Again requires enumerating each possible column in the measure, so could be tedious to maintain.
3. May have issues at total level.

Units Sold Total Method 2b = 
-- If a column is not INSCOPE, then set filter to ALL values of that column
-- otherwise set filter to the currently visible value of that column
VAR Filter_CustomerName =
    FILTER (
        ALL ( Customer[Customer Name] ),
        NOT ISINSCOPE ( Customer[Customer Name] )
            || Customer[Customer Name] IN VALUES ( Customer[Customer Name] )
    )
VAR Filter_CustomerRegion =
    FILTER (
        ALL ( Customer[Customer Region] ),
        NOT ISINSCOPE ( Customer[Customer Region] )
            || Customer[Customer Region] IN VALUES ( Customer[Customer Region] )
    )
VAR Filter_CustomerSegment =
    FILTER (
        ALL ( Customer[Customer Segment] ),
        NOT ISINSCOPE ( Customer[Customer Segment] )
            || Customer[Customer Segment] IN VALUES ( Customer[Customer Segment] )
    )
VAR Filter_ProductCategory =
    FILTER (
        ALL ( Product[Product Category] ),
        NOT ISINSCOPE ( Product[Product Category] )
            || Product[Product Category] IN VALUES ( Product[Product Category] )
    )
VAR Filter_ProductNumber =
    FILTER (
        ALL ( Product[Product Number] ),
        NOT ISINSCOPE ( Product[Product Number] )
            || Product[Product Number] IN VALUES ( Product[Product Number] )
    )
RETURN
    CALCULATE (
        [Units Sold Measure],
        Filter_CustomerName,
        Filter_CustomerRegion,
        Filter_CustomerSegment,
        Filter_ProductCategory,
        Filter_ProductNumber
    )

I much prefer Method 1. It is easier to maintain as the Total measure references each dimension table as a whole. It is not unheard of to use a modelling pattern like this when a dimension has to carry out different roles (e.g. Basket Analysis).

Hopefully that's food for thought at least, and, with some testing, can be used somehow in your actual model!

Regards