Indirect Relationship Needed

[Aaron_Borns]

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!

[Aaron_Borns · Accepted solution]

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.

[Anonymous]

This solution worked really well, thank you

[Aaron_Borns]

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.