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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
SN11112001
New Member

Difference between NON FILTER and FILTER

I have two tables.

Orders -

Order IDCIDQuantity
O1CID_293
O2CID_3100
O3CID_350
O4CID_475

 

Customers -

Customer IDLocation
CID_1Pune
CID_2Mumbai
CID_3Mumbai
CID_4Sambhajinagar


Both the tables are related through the Customer ID column.

Now I want to know those customers whose OrderID was either "O1" or "O4". I wrote the following DAX.

EVALUATE
    CALCULATE TABLE(
        VALUES(Customers[Customer ID]),
        Orders[Order ID] IN {"O1", "O4"}
    )

I got the below Output, which is incorrect.

Customer ID

CID_1
CID_2
CID_3
CID_4



And again, I wrote a new DAX, i.e.

EVALUATE
    CALCULATE TABLE(
        VALUES(Customers[Customer ID]),
        FILTER(
            Orders,
            Orders[Order ID] IN {"O1", "O4"}
        )
    )

Now I am getting the correct Output, i.e.

Customer ID

CID_2
CID_4




I request that anyone please tell me why I am not getting the desired Output in the first case and why I need to use the filter in this situation. Also, please tell me what function or topic I should study because I am doubting.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @SN11112001 

I am assuming you have a single-directional 1:many relationship between Customers & Orders (i.e. the relationship direction is such that Customers filters Orders).

 

The short/intuitive answer is:

Filters applied on columns of a table "propogate" to other tables in the direction of relationships beginning from the original table.

 

This means that, in your first query, the filter applied on the column Orders[Order ID] does not propogate to Customers, and Customers therefore remains unfiltered.

 

What about the second DAX expression?

When a physical table is provided as a filter, that table is treated as the "expanded table", which includes all columns of the table itself plus all columns of related tables that are on the 1-side of a relationship with the table provided as filter (recursively following relationships).

 

This means that, in your second query, the FILTER ( Orders,...) expression refers to the physical table Orders, and the DAX engine therefore interprets this as the expanded Orders table, with the filter on Orders[Order ID] applied. The final filter applied within CALCULATETABLE is a table consisting of the O1 & O4 rows of Orders joined with the related rows of Customers. This has the effect of filtering Customers to just those rows related to O1 & O4.

 

How would I suggest writing this table expression?

I would generally use SUMMARIZE to retrieve the values of any column(s) of a dimension table related to a fact table in any context:

EVALUATE
CALCULATETABLE (
    SUMMARIZE ( Orders, Customers[Customer ID] ),
    Orders[Order ID] IN { "O1", "O4" }
)

 

I recommend these resources:

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

Hi @SN11112001 

I am assuming you have a single-directional 1:many relationship between Customers & Orders (i.e. the relationship direction is such that Customers filters Orders).

 

The short/intuitive answer is:

Filters applied on columns of a table "propogate" to other tables in the direction of relationships beginning from the original table.

 

This means that, in your first query, the filter applied on the column Orders[Order ID] does not propogate to Customers, and Customers therefore remains unfiltered.

 

What about the second DAX expression?

When a physical table is provided as a filter, that table is treated as the "expanded table", which includes all columns of the table itself plus all columns of related tables that are on the 1-side of a relationship with the table provided as filter (recursively following relationships).

 

This means that, in your second query, the FILTER ( Orders,...) expression refers to the physical table Orders, and the DAX engine therefore interprets this as the expanded Orders table, with the filter on Orders[Order ID] applied. The final filter applied within CALCULATETABLE is a table consisting of the O1 & O4 rows of Orders joined with the related rows of Customers. This has the effect of filtering Customers to just those rows related to O1 & O4.

 

How would I suggest writing this table expression?

I would generally use SUMMARIZE to retrieve the values of any column(s) of a dimension table related to a fact table in any context:

EVALUATE
CALCULATETABLE (
    SUMMARIZE ( Orders, Customers[Customer ID] ),
    Orders[Order ID] IN { "O1", "O4" }
)

 

I recommend these resources:

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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