Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
talespin
Solution Sage
Solution Sage

Looking for explanation, not formula

Hi Everyone,

 

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

 

talespin_1-1707303109027.png

 

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?

 

1 ACCEPTED SOLUTION
govindarajan_d
Solution Supplier
Solution Supplier

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 

 

govindarajan_d_0-1707313287539.png

 

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

 

govindarajan_d_1-1707313332835.png

 

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. 

govindarajan_d_2-1707313447932.png

govindarajan_d_3-1707313502439.png

 

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

 

govindarajan_d_4-1707313560963.png

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
HardReads filter context as 'Hard'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
N/AReads filter context as 'N/A'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
RawReads filter context as 'Raw'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
SoftReads 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. 

 

govindarajan_d_5-1707314194675.png

 

For more info:

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

 

 

View solution in original post

2 REPLIES 2
talespin
Solution Sage
Solution Sage

hi @govindarajan_d ,

 

Thanks a ton for putting up such detailed explanation.

 

 

 

 

govindarajan_d
Solution Supplier
Solution Supplier

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 

 

govindarajan_d_0-1707313287539.png

 

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

 

govindarajan_d_1-1707313332835.png

 

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. 

govindarajan_d_2-1707313447932.png

govindarajan_d_3-1707313502439.png

 

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

 

govindarajan_d_4-1707313560963.png

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
HardReads filter context as 'Hard'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
N/AReads filter context as 'N/A'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
RawReads filter context as 'Raw'. Discards it. Puts filter context as 'N/A'. Calculates the sum where MaterialCode = 'N/A' and shows it. 
SoftReads 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. 

 

govindarajan_d_5-1707314194675.png

 

For more info:

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

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors