Forum Discussion
DAX Code Explanation
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.
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!
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