The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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:
Regards
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
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |