cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sangjinhong
New Member

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

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.

 

sangjinhong_2-1620315217247.png

 

Inside DAX , there are  2 expanded tables for Sales and Product:

sangjinhong_4-1620316254374.png

 

 

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
ProductPC Sales
PC30
Mobile30
Grand Total30

 

 

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"

sangjinhong_5-1620316485688.png

 

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 :

 

ProductIDSales QuantityProductIDProduct
1201PC
1101PC
 

 

1 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

Alberto Ferrari - SQLBI

View solution in original post

4 REPLIES 4

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

Alberto Ferrari - SQLBI

View solution in original post

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
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors