DAX Code Explanation

[blazgaj]

Hello,

could somebody please explain me why measure TEST_2 do not act the same way as TEST:

The goal is to calcuate sum of 3 months with bunch of filters:

TEST (correct one):

TEST = 

VAR max_date = MAX('PRD V_DIM_Date'[dateFull])

var m1 = 
CALCULATE(
    SUM('PRD V_FACT_Flat_Planet'[Yearly Cumulative GC]),
            
                'PRD V_FACT_Flat_Planet'[Cons Version] = "100"
               ,'PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10] IN {"0000211001","211004-10"}
               ,DATESINPERIOD('PRD V_DIM_Date'[dateFull],max_date,-3,MONTH)
)

var m2 = 
CALCULATE(
    SUM('PRD V_FACT_Flat_Planet'[Yearly Cumulative GC]),
            
                'PRD V_FACT_Flat_Planet'[Cons Version] = "100"
                ,DATESINPERIOD('PRD V_DIM_Date'[dateFull],max_date,-3,MONTH)
                ,'PRD V_DIM_Business unit'[BU code] = "1010_MN1"
                ,'PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10] IN {"210002-50", "210002-60", "210002-65", "210000-01"}
                ,HLPR_PG[PG_Key] = 1
)

var m3=
CALCULATE(
    SUM('PRD V_FACT_Flat_Planet'[Yearly Cumulative GC]),
            
                'PRD V_FACT_Flat_Planet'[Cons Version] = "100"
                ,DATESINPERIOD('PRD V_DIM_Date'[dateFull],max_date,-3,MONTH)
                ,'PRD V_DIM_Business unit'[BU code] = "3180_MNV"
                ,'PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10] IN {"210002-50", "210002-60", "210002-65", "210000-01"}
                ,HLPR_PG[PG_Key] = 2
)

RETURN m1+m2+m3
                

TEST_2 (not correct):

TEST_2 = 

VAR max_date = MAX('PRD V_DIM_Date'[dateFull])

RETURN

CALCULATE(
    SUM('PRD V_FACT_Flat_Planet'[Yearly Cumulative GC]),
            FILTER(
                ALLEXCEPT('PRD V_FACT_Flat_Planet','PRD V_FACT_Flat_Planet'[BU Code])
                
                ,'PRD V_FACT_Flat_Planet'[Cons Version] = "100" &&
               
                (
                RELATED('PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10]) IN {"0000211001","211004-10"}
                ||
                    (
                    RELATED('PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10]) IN {"210002-50","210002-60","210002-65","210000-01"} &&
                    RELATED('PRD V_DIM_Business unit'[business unit code]) = "1010_MN1" &&
                    RELATED(HLPR_PG[PG_Key]) = 1
                    )
                ||
                    (
                    RELATED('PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10]) IN {"210002-50","210002-60","210002-65","210000-01"} &&
                    RELATED('PRD V_DIM_Business unit'[business unit code]) = "3180_MNV" &&
                    RELATED(HLPR_PG[PG_Key]) = 2
                    )
                )   
            ), DATESINPERIOD('PRD V_DIM_Date'[dateFull],max_date,-3,MONTH)
)

I am using ALLExcept to remove filter context exept for BU. I am using FILTER beacuse I have a three blocks of "and or" conditions with different tables which wont work strictly inside the calculate which in TEST is written with 3 variables.
I do not understand why TEST_2 do not work with Filter Context for Division and behaves like ALL function.

I am a newbie in DAX maybe could please point me how this can be written in different way at last explanation with TEST_2 doesnt work is also fine 🙂

Thanks! 

[v-hashadapu · Accepted solution]

Hi @blazgaj , Thank you for reaching out to the Microsoft Community Forum.

The difference between your TEST and TEST_2 measures comes down to how filter context is managed in DAX, especially with ALLEXCEPT. In TEST, using three separate CALCULATE blocks for each scenario means each block keeps the original filter context from your visual, including Division. So, when your visual slices by Division, TEST changes per row because it sees the Division filter.

In TEST_2 you’ve used ALLEXCEPT to clear all filter context on the table except for BU Code. That means any context applied for Division by your visual is ignored, so every row shows the same result regardless of which Division is selected. This mimics the behavior of ALL rather than preserving context for Division; ALLEXCEPT only keeps BU Code and drops everything else. That’s why your TEST_2 doesn’t break down by Division. To do what TEST does, don’t use ALLEXCEPT for business logic relying on multiple contexts, instead, use separate variables or CALCULATE blocks for each logical segment to keep filter context.

ALL function (DAX) - DAX | Microsoft Learn

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

[djurecicK2]

@v-hashadapu This is a great explanation.

[blazgaj]

Great - thanks for explanation !

Any sugesstion how I could write this DAX code with multiple filter block in some more sophisticated wat rather than in measure TEST

Thanks again!

[v-hashadapu]

Hi @blazgaj , Thank you for reaching out to the Microsoft Community Forum.

You can make your DAX measure with several complex filter blocks more elegant by using the CALCULATE function with multiple filter arguments either by using boolean logic (AND/OR) directly or by combining conditions inside a single FILTER block. DAX allows you to include several conditional predicates within CALCULATE; for example, you can write CALCULATE([Sum], condition1, condition2, ...) and even mix these for clarity. If your filters involve columns from related tables, use RELATED or RELATEDTABLE or nest complex logic inside FILTER for more control.

For bigger logic blocks with OR/AND rules, you can use a single FILTER function applied to your table, combining all necessary conditions with DAX’s logical operators. For frequent patterns, consider using SWITCH for branching logic or creating reusable calculated tables to simplify measures. Proper structure not only improves readability and maintainability, but also keeps your code tightly aligned with filter context from the visuals.

Below is an example measure, you can refer. It may not fully work for you as it is an example only, you may need to make changes according to your scenario.

SumOfThreeMonths =
VAR MaxDate = MAX('PRD V_DIM_Date'[dateFull])
VAR NodesA = { "0000211001", "211004-10" }
VAR NodesB = { "210002-50", "210002-60", "210002-65", "210000-01" }
RETURN
CALCULATE(
SUM('PRD V_FACT_Flat_Planet'[Yearly Cumulative GC]),
'PRD V_FACT_Flat_Planet'[Cons Version] = "100",
DATESINPERIOD('PRD V_DIM_Date'[dateFull], MaxDate, -3, MONTH),
FILTER(
'PRD V_FACT_Flat_Planet',
RELATED('PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10]) IN NodesA
||
(
RELATED('PRD V_HIER_CS_ITEM_NTB_S'[Node Level 10]) IN NodesB
&& (
RELATED('PRD V_DIM_Business unit'[business unit code]) = "1010_MN1" && RELATED(HLPR_PG[PG_Key]) = 1
|| RELATED('PRD V_DIM_Business unit'[business unit code]) = "3180_MNV" && RELATED(HLPR_PG[PG_Key]) = 2
)
)
)
)

Filter functions (DAX) - DAX | Microsoft Learn

CALCULATE function (DAX) - DAX | Microsoft Learn

Specifying multiple filter conditions in CALCULATE - SQLBI