topic How understand ”filter argument override the exsiting filter on the same column” from Expanded Table in DAX Commands and Tips

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

sangjinhong — Thu, 06 May 2021 16:11:25 GMT

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

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

amitchandak — Thu, 06 May 2021 16:18:58 GMT

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/

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

sangjinhong — Thu, 06 May 2021 22:19:33 GMT

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

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

AlbertoFerrari — Thu, 06 May 2021 22:41:29 GMT

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

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

sangjinhong — Thu, 06 May 2021 23:48:45 GMT

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