Forum Discussion

ViktorL_2's avatar
ViktorL_2
Frequent Visitor
2 years ago
Solved

Understanding evaluation context

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 t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ViktorL_2
    Frequent Visitor
    2 years ago

    Hi Anonymous

     

    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