How to force bi-directional relationship in DAX query

Question

Hi all,

I'm writing a DAX query in SSRS that requires data from multiple tables (1 fact and 2 dimensions) in a SSAS Tabular Model
The model is basic using only 1-n single-direction relationships.
For my query to work I need to adapt one of these relationships to bi-directional. I don't want to alter my Tabular model, because of the impact it might have on other processes (eg circular relations)

How can I force my dax query hereunder to use a bi-directional relationship between dim1 and dim2.
How it is now: Dim1 ---> Fact1 <--- Dim2
How I want it to act: Dim1 ---> Fact1 <---> Dim2

EVALUATE
SUMMARIZECOLUMNS (
'Dim1'[Col1],
'Dim2'[Col2],
FILTER ( ...),
"Measure1", [Measure1]
)

Thanks

kr
geert

Answer 1

What you are probably trying to do is to calculate some measure off Dim2's data while filtering/slicing it by Dim1 (and perhaps by Fact1). Logically, this turns Dim2 into Fact2, so your model becomes Dim1 ---> Fact1 <--- Dim2 <-1:1-> Fact2. And this begs the original tabular M2M solution of adding Fact1 as a filter to the calculation, capitalizing on the table expansion feature.

Answer 2

Hi,

For this we need to define a new measure which have CROSSFILTER on both direction in Fact1 and Dim2.
For example :

MeasureBi = CALCULATE([Measure1], CROSSFILTER(Fact1[Key2],Dim2[Key2],both))   

Then, use this measure in your new table

EVALUATE  
SUMMARIZECOLUMNS (  
'Dim1'[Col1],  
'Dim2'[Col2],  
FILTER ( ...),  
"Measure", [MeasureBi]  
)  

Reference :

CROSSFILTER
Avoid bidirectional relationships (brute force method)
Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November