Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sangjinhong
Regular Visitor

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

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors