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.
How to force bi-directional relationship in DAX query
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
FILTER ( ...),
Have you resolved the issue ? Any feedback on this case?
Sign in to comment
Sort by: Most helpful
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] )
Avoid bidirectional relationships (brute force method)
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