Clashing Page Filters Different To Visible Arguments

[jasperj101]

Hello, 

I have an issue with clashing page filters in a report, I have made a simple model below to highlight this issue: 

I have a table of questions as below, these questions can have different types with Question1 being able to have type A, B or C whereas Question2 can only be of type B. 

I want to count the values of question 1 and display this in a card, I have a countrows measures that counts the rows in the above table and then a question1 Count measure that calculates [Count] with the filter argument being [QuestionID] = 1

I have 2 page filters in the report, QuestionID and Type, I have asserted that Type can be A, B OR C. The issue arises when I then also filter by QuestionID:

When the filter argument in the pane is (QuestionID = 2) this invisibly alters the Type filter argument to (Type = B) as QuestionID 2 can only be type B. This changes the Question1 Count from 6 to 2. 

If there is any way to fix how these filters interact that would be great, my actual working scenario is much more complex where multiple filter combinations can yield different results and I cannot write separate measures redefining the filter context for the counts to take into account every combination. Ideally this needs to controlled by the page filters in my reports. 

Thanks, 

J

[Anonymous]

Hi,dharmendars007 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@jasperj101 .I am glad to help you.
Based on your description, you seem to have a question about the role of the filter fields in the DAX code internal filter criteria and Filters area in Power BI desktop, here is my test.
I have reduced the test data you have given.

I have created three measurements.
Restore the measure that you think is causing the problem.

Count = 
COUNTROWS('Questions')

Question1 Count = 
CALCULATE(
    [Count],
    Questions[QuestionID]=1
    )

_Filters =
CALCULATE ( [Count], FILTER ( 'Questions', 'Questions'[QuestionID] = 1 ) )

_ALL =
CALCULATE (
    [Count],
    ALL ( 'Questions'[QuestionID] ),
    ALL ( Questions[Type] ),
    'Questions'[QuestionID] = 1
)

When the filter condition of the filters area is QustionID is 2 & Type is A,B,or C

1. For measure:[Question1 Count]

Question1 Count = 
CALCULATE(
    [Count],
    Questions[QuestionID]=1
    )

Why is it “incorrectly displaying 2”, it looks like it's not executing the dax code of
Questions[QuestionID]=1
Note that in fact there is no problem because "QustionID is 2 & Type is A,B,or C"
The two filters themselves will filter each other, when QustionID is 2 is selected, according to your own description, the Type is only B, so in fact, only the number of Type = B, and QuestionID = 2 will be filtered out, because at this time, there is no function to clear the external filters set up by the calculate function in dax, so the filters area is set up to clear the external filters, so there is no problem. Therefore, at this time, filters region set the screening conditions is greater than the measure dax code is the condition, because at this time, all the page dax code calculation of their computing environment must first follow the Filters region in the screening conditions, and then the implementation of their respective code in the screening conditions (the two is not a hierarchical relationship), so the Question1 Count prioritizes the calculation of QustionID is 2.
It may seem like the data is being presented incorrectly, but I have come to the conclusion after my testing and discussions with other members of the team that this is due to a prioritization issue with the execution of the filtering environments.

If you want to make sure that the calculation environment is not affected by external filters in the calculate function, you need to use the filters function to limit the actual calculation environment in the dax
like measure:[_Filters]

Or you can use the all function in the calculate function to clear the external filters for a specific column or table.
like meausre:[_ALL]
In fact, for the use of the calculate function, the determination of the computational context is very important because it creates a new computational context, specifies the internal filters of the function, and strongly alters the external filtering context.
When internal and external filters act on the same column, the filter specified by the calculate function parameter has priority.
So you need to be based on what you actually want to get the effect, carefully set the calculation environment in the calculate function, and it should be noted that, try to avoid internal and external filters at the same time on the same data column (especially the filter conditions are inconsistent), which can easily lead to the actual calculation of the environment and the expected inconsistency, resulting in calculation errors.

And the problem you encountered, the best you can do is to modify the dax itself, rather than modify the filters filters, because it acts on the entire page of visual, generally has a higher filter priority (unless the use of all function, etc. to force the clearing of external filters), which is more conducive to the production of reports.

I hope the following article will help you
URL:
Specifying multiple filter conditions in CALCULATE - SQLBI
Power BI How-to: DAX | Use CALCULATE and FILTER (learndatainsights.com)

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.

[dharmendars007]

Hello @jasperj101 , 

You can try this measure where you can use "Remove Filter" or "ALL" Function  ot your existing measure..

Question1 Count (Filtered) =
CALCULATE(
COUNTROWS('QuestionsTable'),
REMOVEFILTERS('QuestionsTable'[QuestionID]),
REMOVEFILTERS('QuestionsTable'[Type]),
'QuestionsTable'[QuestionID] = 1
)

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[jasperj101]

Thank you for the reply, 

This would work, however in my working scenario I need to retain the filters on Questions[Type]. Imagine in the scenario above I only wanted to count Question 1 where Category is A or B - Removing filters would also count C which I dont want. I also cannot just assert int he count measure Category = A or B because on different pages of the report I have many different combinations. 

I tried just removing filters from Question[QuestionID] but the Question[Type] filter is still being filtered by the selection in Question: 

It is as if the filter argument in the measure does not propagate between the 2 page level filters.

[jasperj101 · Accepted solution]

Hi, 

After a bit more reading my colleagues found this following article on SQLBI that is very similar to the scenario we came up with, this is a feature known as auto-exist when SUMMARIZECOLUMNS is executed to display a visual. 

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

The way to avoid this is to separate the 2 columns into their own dimension tables to avoid auto-exist irreversibly filtering out combinations of values it does not see in its initial context. (Star schema wins again!)