Understanding evaluation context

[ViktorL_2]

Hi PowerBI experts,

I have super simple data model (not real world one, just for learning purposes) consisting of just 1 table (called Data) with 2 columns (PERIOD and SUBJECT_CODE) and no more than 15 rows (see bellow). There are three SUBJECT_CODEs {A, B, C} and six periods {2, 3, 4, 5, 6, 7}. Table does not contain all their combinations, some SUBJECT_CODEs are not present for some PERIODs. PERIODs are represented as integers and not as dates for simplicity.

 I have one measure:

Test =

VAR MaxDate = MAX(Data[PERIOD])

VAR Result =

CALCULATE(

     COUNTROWS(Data),

     Data[PERIOD] <= MaxDate

)

RETURN Result

This measure is put into table visual or line chart visual in the following set up (picture bellow). Slicer for SUBJECT_CODE filters  A and B, and slicer for PERIOD filters 2 to 7. The point of the measure should be to count number of existing combinations in Data table for selected SUBJECT_CODEs up to given PERIOD in the visuals.

And I can not undestand why my measure Test produces results different than what I would expect based on my understanding of the evaluation context. Probably I am missing something very fundamental.

Specifically the question is, why value of Test (in other words number of rows filtered by given evaluation context) for PERIOD 6 is equal to 3 and not equal to 7 as I would expect. It seems that only SUBJECT_CODE B is present in the evaluation context of the measure for PERIOD 6 despite the fact that SUBJECT_CODE slicer filters subjects A and B. I do not understand why/how SUBJECT_CODE A drops out of evaluation context of the measure calculated fot PERIOD 6.

Please could somebody explain me where I am wrong?

Many thanks in advance.

By the way, I would gratefully share my tiny pbix file if needed, however I am new here and I do not know how to attach any file to the post.

Viktor

[v-zhengdxu-msft]

Hi @ViktorL_2 

Slicers allow users to select columns that are used to slice and dice values in Power BI visuals.

why value of Test for PERIOD 6 is equal to 3 and not equal to 7 as you expected:

Your slicer uses fields SUBJECT_CODE divides the table into three areas:

Easily to find that the value 3 you get is calculated separately for A and B, and then added, like this(0+3=3):

The reason for this is that the filtering range of your measure is limited by the slicer, which you can use the all(), allselected(), allexcept(), and removefilter() functions to remove unwanted filtering effects.

Like this measure:

test2 =
VAR maxDate = MAX('Table'[PERIOD])
VAR result =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[PERIOD]<=maxDate
        )
    )
RETURN result

The result is as follow:

Here's a blog about contextual filtering

Understand the Filter Context and How to Control i... - Microsoft Fabric Community

Best Regards,

Zhengdong Xu

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

[ViktorL_2]

Hi @v-zhengdxu-msft

First of all, thank you very much for your reply and effort to help me. Secondly, please apologize my late reaction.

Unfortunately, I have to say that even if I went through your email again and again I am still confused why "my" measure Test works like it works (of course this is my fault, not yours at all). 

You wrote that the value 3 I get is calculated separately for A and B, and then added, like this(0+3=3). From what I know about DAX this "step-by-step" mechanism of calculation is somtehing that I have never read or heard of. But it is not so important at this moment.

My understading of how evaluation context in my measure Test is build up is as follows. Assume that slicer for PERIOD filters A and B, and slicer for SUBJECT_CODE filters 2 to 6 (situation like in the last picture of your email) and that we calculate measute for PERIOD 6 in line chart or table visual :

1. So, the outer filter context consist of PERIOD = {2, 3, 4, 5, 6} (from slicer), PERIOD = {6} (from visual), which intersects to PERIOD = {6}, and SUBJECT_CODE = {A, B} (from slicer)
2. MaxDate calculated in the outer filter context then results in 6
3. Condition  Data[PERIOD] <= MaxDate inside CALCULATE returns PERIOD = {2, 3, 4, 5, 6} and this inner filter context for PERIOD overwrites outer context (PERIOD = {6})
4. CALCULATE does not change filter context for SUBJECT_CODE (no condition or modifier regarding SUBJECT_CODE is present in it), so the outer filter context SUBJECT_CODE = {A, B} from slicer is stil valid
5. The final filter context in which measure Test is calculated is PERIOD = {2, 3, 4, 5, 6} and SUBJECT_CODE = {A, B}, which should filter 7 rows of Data table {(A, 2), (A, 3), (A, 4), (A, 5), (B, 4), (B, 5), (B, 6)}

Of course, there is some point in my chain of reasoning which is wrong, because result is not 7 but 3. Would you, please, be able to pinpoint where exactly it is.

Finaly, I would like to ensure you that I will accept your solution, even if you decide to not answer to my second email. 

Best regards 

Viktor

[ViktorL_2 · Accepted solution]

I have finally found the answer to "my riddle" in the following post:

https://community.fabric.microsoft.com/t5/Desktop/Unusual-Filter-Behavior-Filter-Context-Not-Being-R...

This above-mentioned post deals with very similar problem as I was struggling with and refers to the following very clear article on auto-exist behaviour of SUMMARIZECOLUMNS function in some specific situations. It exactly answers to the questions raised in my post.

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