Forum Discussion
Indirect Relationship Needed
I have three tables with two relationships in a DirectQuery .pbix:
Active From: Table (Column) To: Table (Column) Cardinality Cross filter direction
TRUE Table3 (id) Table2 (id) Many to One (*:1) Single
TRUE Table1 (id) Table2 (id) Many to One (*:1) Single
Here are the columns of interest for each table:
Table Column Data Type
Table1 id Whole Number
Table1 model Text
Table2 id Whole Number
Table2 product_line Text
Table3 id Whole Number
Table3 item Text
How do I create a table visualization of Table3 items that are sliced by Table1 models?
Considerations:
- I cannot make a direct relationship between Table1 and Table3 because the id column is not unique for either table.
- There is a limited subset of DAX allowed in DirectQuery format.
Thank you!
This solution is even more brilliant. No messing with the relationship modeling, I just added this measure to the table visualization and let DAX do the rest. I go the inspiration from this article by Marco Russo.
use_cases = COUNTX(table3,
CALCULATE(VALUES(table1[id]),
FILTER(table1,
table1[id] = table3[id]
)
)
)Hope this helps someone.
3 Replies
This solution is even more brilliant. No messing with the relationship modeling, I just added this measure to the table visualization and let DAX do the rest. I go the inspiration from this article by Marco Russo.
use_cases = COUNTX(table3,
CALCULATE(VALUES(table1[id]),
FILTER(table1,
table1[id] = table3[id]
)
)
)Hope this helps someone.
Came to a solution after finding this article from Microsoft. I also recommend this article by sqlbi.com.
First I turned on bi-directional filtering, which is currently a Preview Feature in BI Desktop found under Options.
Then I made the table1:table2 relationship cross filter in both directions.
Although this solution is not exactly what I wanted because now my slicers are bi-directionally filtered, it gets me more than halfway.
