Understanding DAX Filter Context with Nested Measures in Power BI

[AlexanderPowBI]

Hello everyone,

I am working on a Power BI project where I have encountered an issue with DAX measures and filter contexts. To make it simple, let's say I have a base measure defined as:

```DAX
A = COUNTROWS(myTable)
```

I have also created slicers in my report where I can filter the data based on “county” and “salesperson” columns.

What I want to achieve is to create a new measure, let’s call it `B`, which should calculate the value of measure `A` but ignoring the filter applied on the “salesperson” column while still respecting the “county” filter.

My first approach was to define measure `B` as:

```DAX
B = CALCULATE(
[A],
ALL(myTable[salesperson])
)
```

I expected this to work, but it seems like the “salesperson” filter is not being completely ignored. The result changes when I select different salespersons, which is not what I intended.

To ensure the accurate calculation of `B`, should I redefine it without referencing measure `A`, like so:

```DAX
B = CALCULATE(
COUNTROWS(myTable),
ALL(myTable[salesperson])
)
```

I am trying to understand why my initial approach did not work as expected. Is it due to the filter context brought by measure `A` when referenced in measure `B`?

I would appreciate any insights or explanations on how the filter context is working here and how I can correctly define measure `B` to get the desired output.

I have many of these (nested) measures to define so I really need to understand why this is not working 

Thank you!

[lbendlin]

1. Use EVALUATEANDLOG()  with the DAXDebugOutput tool to monitor the transitions.

2. Do not nest measures, especially not during initial development. 

[AlexanderPowBI]

Hello,

Thank you for your response.

1. I appreciate the insights provided, and I'll certainly delve deeper into the aspects you mentioned. However, I'm considering revising my data model, especially since I didn't segregate the salesperson data into a separate dimension table. I've observed that the ALL function operates seamlessly when applied to slicers on dimension tables. For instance, I have a location table which I frequently use for slicing, and the ALL function in measures seems to function without hitches here. Could this potentially be the crux of the issue?

2. On a more general note, is it recommended not to nest measures? I was under the impression that nesting measures facilitates code reuse, thereby enhancing efficiency. Were you suggesting this approach solely for the developmental phase to ensure everything runs smoothly, or is this a broader guideline?

[lbendlin · Accepted solution]

Revising the data model is always a good option. Oftentimes report issues go away when the data model is more robust.  You can choose the level of normalization - don't go overboard though, you won't be happy with a 6NF for example.  Generally the data model has to follow the business question.

My personal recommendation is not to nest measures. Others will have different opinions.  I already have a hard time following the context transitions with a single measure, I don't need the mental gymnastics with nested measures.  The "code reuse" aspect is valid, but only if you are absolutely sure that your measure is as optimized as it can be.  Otherwise you risk re-using bad measures over and over.