Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hi Everyone,
I need help with explanation for the table visual on the left with respect to measure.
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?
Solved! Go to 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/
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/
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
44 | |
37 | |
25 | |
24 | |
23 |