How to force bi-directional relationship in DAX query

geert vanhove 116 Reputation points
2020-12-02T16:58:55.667+00:00

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

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,786 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,411 Reputation points
    2020-12-03T07:44:01.693+00:00

    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.

    0 comments No comments

  2. Lukas Yu -MSFT 5,816 Reputation points
    2020-12-03T07:55:24.017+00:00

    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

    0 comments No comments