Looking for explanation, not formula

[talespin]

Hi Everyone,

I need help with explanation for the table visual on the left with respect to measure.

MRequiredMeasure =

CALCULATE(

       SUM(FactTable[Sales]),

       DimMaterial[MaterialCode] = "N/A"

)

If you look at screenshot, I have shared source tables, datamodel and Table visuals. If I add my measure to the table visual on the right, it results into a visual on the left.

Why for 202305 there are additional records. When fiter context is present

Filter context from Visual   YearMonth = 202305 AND MaterialCode = "N/A"

Filter context from measure MaterialCode = "N/A"

I understand that this will override the outer filter context for MaterialCode only,  But why the three records circled in green are generated?

[talespin]

hi @govindarajan_d ,

Thanks a ton for putting up such detailed explanation.

[govindarajan_d · Accepted solution]

Hi @talespin ,

As you have correctly said, the filter you have used overrides the existing filter context and replaces that. This includes the filter coming from Material being in the table. 

Let's consider you have sum of sales in your table without any other column 

The moment, we drag and drop the column 'MaterialID' column, the values get split accordingly. 

How does Power BI do that? For each row as it calculates the sum, it takes into consideration the MaterialID and uses it as a filter in the the corresponding table. This is the filter context that comes with the column being present in the table.

In your version, if you used the measure just as it is, you will find that it just returns 50 which is correct. Even if we add Month, it won't change. 

But as soon as I add 'MaterialCode', the problem you stated appears. 

We know that the formula used corresponds to:

CALCULATE(SUM(Sales), FILTER(ALL(MaterialCode), Material = 'N/A'))

So we know that it overrides the filter context and replaces it with N/A

This is because internally, Power BI checks the filter context corresponding to that row. Let's filter to Month = 5 and see what Power BI does internally:

MaterialCode	MeasureTest what it does internally
Hard	Reads filter context as 'Hard'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it.
N/A	Reads filter context as 'N/A'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it.
Raw	Reads filter context as 'Raw'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it.
Soft	Reads filter context as 'Soft'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it.

So, for every row it discards the filter context and creates new filter context that comes from CALCULATE. To keep existing filters, you will use KEEPFILTERS. And as you can see below, it combines the existing filter context from column and the one in formula and then calculates the sum. That is why you see 50 only for N/A in second measure. 

For more info:

https://www.sqlbi.com/articles/using-keepfilters-in-dax/