How understand ”filter argument override the exsiting filter on the same column” from Expanded Table

[sangjinhong]

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

[AlbertoFerrari · Accepted 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

[sangjinhong]

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

---

[amitchandak]

@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/

[sangjinhong]

I just want to understand how the filter from matrix table and filter argument from CALCUALTE apply to expanded table inside the DAX