Help with measure to ignore filters

[Fusilier]

Hi, hope somebody can help please?

My knowledge of DAX is patchy to say the least!

I have this matrix visualisation:

I'm trying to adapt the measures for the 'AnswerCount' and 'Total Answers' columns in the visualiation so that they don't change if I apply any filters just on the visualisation but still reflect any filters on the page (if that makes sense?)  I have a number of potential filters: Age group, Ward, Gender, Ethnicity etc. etc, that I may apply, depending on what I choose to use, either on the page or on the visualisation.

So, for example, if I apply the age group filter on the visualisation, and select say '18-24', the '2AnswerCount' and '2Total Answers' columns will change to reflect the selected age group, but the 'AnswerCount' and 'Total Answers' colums will stay as they are.

The measures as they currently stand are:

AnswerCount:

AnswerCount = COUNTROWS('Combined Table')

Total Answers:

Total Answers = CALCULATE(

    COUNTROWS('Combined Table'),

    REMOVEFILTERS('Combined Table'[Answer]))

Just not sure how to achieve this. Do I add ALL or ALLEXCEPT or something to the measures? I'm just not sure how to achieve this.

Hope this makes sense?

Any help with this would be appreciated.

[v-jtian-msft · Accepted solution]

Hi,@Shravan133 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

The suggestions made by Shravan133 are good, here are some additional suggestions from me.

For your report, you mentioned the existence of multiple filters, which also include page filtering and visual filtering.
So I think you need to seriously consider the use of the ALL,ALLEXCEPT function.
In fact, based on your description, here's my guess as to what you are looking for
You want to adjust the measure "AnswerCount" and "Total Answers" in the report.
The changes are as follows.
When applying filters on visual, these two measures will not change, but when applying filters on page, it will reflect the corresponding changes.

For example:

When applying filters on a visual chart (e.g. age group filter), the columns "AnswerCount" and "Total Answers" remain unchanged.
When applying filters on a page, the "AnswerCount" and "Total Answers" columns reflect the changes.
You mentioned using the ALL, ALLEXCEPT functions, I think you can take the ALLSELECTED function into account as well, and similarly for keeping the selections on the slicer and ignoring the filters on the visual, the removefilters function you're using is using the correct one.
You can try to use these functions together ( ALL SELECTED(), REMOVEFILTERS()  ).
Here are my tests:
my test data :

AnswerCount = COUNTROWS('Combined Table')


Total Answers = CALCULATE(
    COUNTROWS('Combined Table'),
    REMOVEFILTERS('Combined Table'[Answer]))



M_AnswerCount = CALCULATE(
    COUNTROWS('Combined Table'),
    ALLSELECTED('Combined Table')
)


M_allTotal Answers = CALCULATE(COUNTROWS('Combined Table'),ALL('Combined Table'))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fusilier]

Hi,

Thank you. I've had a play using the ALL and ALLSELECTED functions and I think they were what I was looking for. Haven't tried out ALLSELECTED or REMOVEFILTER yet, but I will. Sometimes DAX seems very confusing to me! Thank you for your comprehensive reply, much appreciated

[Shravan133]

try this: 

AnswerCount =
CALCULATE(
COUNTROWS('Combined Table'),
ALL('Combined Table'[Answer]) -- Specify the column or table to ignore filters on the visualization
)

Total Answers =
CALCULATE(
COUNTROWS('Combined Table'),
ALL('Combined Table'[Answer]) -- Specify the column or table to ignore filters on the visualization
)

AnswerCount and Total Answers measures use the ALL function to ignore any filters applied on the 'Answer' column within the visualization. This ensures that these measures show values without being affected by visual-level filters but will still respect any page-level filters (e.g., Age group, Ward, Gender, Ethnicity).