How to keep one visualisation filter result fixed whilst drilling down another on the same visual

[EVANSOL78]

Hi,

I am trying to demonstate on a stacked column chart the Total number of smokers in comparison with of the total number of smokers, which had a smoking pathway completed. I have been able to filter either the Smoking Pathway =Y and Smoking Status = 1 independantly but when I apply both filters at the same time to the visual it filters down to the total number of smokers that had a smoking pathway completed. 

Can you please advise how I can achieve this visual? 

[danextian · Accepted solution]

Hi @EVANSOL78 ,

It's hard to imagine what you are trying to achieve without seeing a sample data and a sample result. But  let me give this a try.

You case use ALL so the value stays the same regardless of the relevent filter.

=
CALCULATE (
    [Your Measure Here],
    FILTER (
        ALL ( 'Table'[Smoking Pathway], 'Table'[Smoking Status] ),
        'Table'[Smoking Pathway] = "Y"
            && 'Table'[Smoking Status] = 1
    )
)

[WanderingBI]

Hi,

If you want to show a field in a visual ignoring the filters (the filter context) that you have set for the visual you can use a measure.

As an example if you table looks like this:

You could create a measure that counts all rows (to get the number of smokers) and then tell it to ignore the filters:

Smokers without Filter = CALCULATE(COUNTROWS(Smoker),ALL(Smoker))