Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I created a very simple model with a one-to-many relationship, to better understand how ”the CALCULATE filter argument on one column override the existing filter on the same column“ from "Expanded Table" view.
Inside DAX , there are 2 expanded tables for Sales and Product:
PC Sales =
CALCULATE (
SUM ( 'Sales'[Sales Quantity] ),
'Product'[Product] = "PC"
)
When I created a measure [PC Sales] as above. I know the filter for the value ”PC“ on column 'Product'[Product] overvided any filter on same column 'Product'[Product], so, if I put the 'Product'[Product] on the row , and , [PC Sales] on values to creat a matrix in Power BI DESKTOP, every row showing the sales quantity of PC
Matrix in Power BI | |
Product | PC Sales |
PC | 30 |
Mobile | 30 |
Grand Total | 30 |
The question will be, if focus on row Mobile I understand the filter context from "Expanded Table" view, does it application step are the follows?
Step 1 : Row Mobile from matrix table has a filter to 'Sales Expanded Table',so, the original filter context is : 'Sales Expanded Table' filtered by the value "PC"
Step 2 : Filter argument from CALCULATE has a "PC" filter on same column 'Product'[Product], so, DAX deleted the existing filter ”Mobile“ on column 'Product'[Product], then add a "PC" filter, so the new filter context will be :
ProductID | Sales Quantity | ProductID | Product |
1 | 20 | 1 | PC |
1 | 10 | 1 | PC |
Solved! Go to Solution.
The filter from the matrix is on the PC column only. Expanded tables play a role when you filter an entire table, which is not what you are doing. CALCULATE ( ..., FILTER ( Sales, Sales[Q] = 1 ) ) would be a table filter. There, the expanded table plays some role. But neither the matrix, nor your code apply filters to tables: you are filtering only columns. Therefore, expanded tables are not part of the game here.
You can find some more info here: https://www.sqlbi.com/articles/expanded-tables-in-dax
The filter from the matrix is on the PC column only. Expanded tables play a role when you filter an entire table, which is not what you are doing. CALCULATE ( ..., FILTER ( Sales, Sales[Q] = 1 ) ) would be a table filter. There, the expanded table plays some role. But neither the matrix, nor your code apply filters to tables: you are filtering only columns. Therefore, expanded tables are not part of the game here.
You can find some more info here: https://www.sqlbi.com/articles/expanded-tables-in-dax
thanks Alberto.
When I read the section "Filter propagation", try to understand how the filters on 'Product'[Product] from matrix and filter argument from CALCULATE fitlered the expanded version of Sales.
so I came out my understanding:
The filter from matrix filtered the expanded version of Sales first then filter from CALCUATE argument filtered the resulting expanded version of Sales--- is that correct? thanks.
-----------------------------------------------------------------------------------------------------------------------
When you filter Product[Product], all the tables that contain that column – either native or related – are filtered.
Thus, Sales is filtered by Product[Product] because the expanded version of Sales contains Product[Product].
@AlbertoFerrari wrote:The filter from the matrix is on the PC column only. Expanded tables play a role when you filter an entire table, which is not what you are doing. CALCULATE ( ..., FILTER ( Sales, Sales[Q] = 1 ) ) would be a table filter. There, the expanded table plays some role. But neither the matrix, nor your code apply filters to tables: you are filtering only columns. Therefore, expanded tables are not part of the game here.
You can find some more info here: https://www.sqlbi.com/articles/expanded-tables-in-dax
@sangjinhong , I am not able to get what you said after
Matrix in Power BI | |
Product | PC Sales |
PC | 30 |
Mobile | 30 |
Grand Total | 30 |
Can you explain what is needed and you are not getting what.
To understand why you are getting all 30 rows http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/
I just want to understand how the filter from matrix table and filter argument from CALCUALTE apply to expanded table inside the DAX
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |