Difference between NON FILTER and FILTER

[SN11112001]

I have two tables.

Orders -

Order ID	CID	Quantity
O1	CID_2	93
O2	CID_3	100
O3	CID_3	50
O4	CID_4	75

Customers -

Customer ID	Location
CID_1	Pune
CID_2	Mumbai
CID_3	Mumbai
CID_4	Sambhajinagar

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.

[OwenAuger · Accepted solution]

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:

• Classic article by Jeffrey Wang on cross table filtering 
• Expanded Tables in DAX by SQLBI 
• https://www.sqlbi.com/tv/expanded-table-the-whiteboard-04/ 

Regards