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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.