Circular Relationship

[srlabhe]

Hi All,

I have below structure in my Power BI Model

Now I want to use inactive join used between Date Dim and Daily Complaints without it going through active joins.

Userelationship cant be solution as I cant have all measures recreated with USerelationship in Daily Complaints as there are plenty of measures there

Also there would be another Dim Tables need to be added in the above structure which can land up in same above situation.So cant have another set of FAX with userelationship

Cant change the Cross Filter Direction of any other join to Single because it says "The filter direction you selected isn't valid for this relationship"

Please guide, any design change also welcome here please.

[Greg_Deckler · Accepted solution]

@srlabhe If you want to change the Cross Filter Direction of any relationship, just lie to your data model and tell it that the relationship is 1 to many or many to 1.

Column Level Security - Microsoft Power BI Community

[srlabhe]

@Greg_Deckler Yes I could lie to model and change the cross filter direction but still cant use the Inactive relationship. I have added another DIM table PRODUCT_INFO_DIM as below ,

and now I wana calculate Daily Complaints Qty as 1 DAX in such way that

1. It should follow DATE_DIM -> Daily Complaints join when I select any DATE_DIM field in Report or 

2.It should follow PRODUCT_INFO_DIM -> Daily Complaints join when I select any PRODUCT_INFO_DIM field in Report 

similarly i may have few more dimension tables to add here and the previously created DAX should work with new join between New Dim and Daily Complaints.

Please suggest

[Greg_Deckler]

@srlabhe Pretty sure what you are after are Calculation Groups. Calculation groups in Analysis Services tabular models | Microsoft Learn

[srlabhe]

@Greg_Deckler or may be I can use multiple userelationship in 1 DAX

[Greg_Deckler]

@srlabhe It's possible. Perhaps if you had a toggle like a disconnected table something like:

Column1

Option 1

Option 2

And perhaps a structure like this:

Sales = 
  SWITCH( SELECTEDVALUE('Disconnected Table'[Column1],
    "Option 1", SUM('Sales'[Value]),
    CALCULATE( SUM('Sales'[Value]), USERELATIONSHIP('Table1'[Column], 'Table2'[Column]))
  )

[srlabhe]

Thsi is possibile only if we know what column we are would select in report visual like 

'Disconnected Table'[Column1]

 but we use any column from disconnected table and hence cant use Switch for so many columns  

[Greg_Deckler]

@srlabhe Not sure I am following you. The disconnected table is simply there to allow you to switch between which calculation you want the measure to return. So there would only be 1 column and 2 rows in theory. Again though, Calculation Groups were designed for this situation specifically.

[srlabhe]

@Greg_Deckler Hmmm how to explain ? Actually I have your book of expert in power bi modeling , what's section of calculation group , let me refer and see if I can implement the same 

[Greg_Deckler]

@srlabhe Should just be something like:

CALCULATE(SELECTEDMEASURE(), USERELATIONSHIP('Table1'[Column1], 'Table2'[Column1]))

[srlabhe]

@Greg_Deckler yes this is what I have tried 

[srlabhe]

@Greg_Deckler would this work in report which is connected to live to Power Bi dataset based on direct query 

[Greg_Deckler]

@srlabhe As long as it is in a measure and not a calculated column it should work. Also, you need to make sure that the tables do not have any RLS defined.

[srlabhe]

@Greg_Deckler whats RLS defined table?

[Greg_Deckler]

@srlabhe Row Level Security (RLS) USERELATIONSHIP will not work on relationships where either table involved has RLS rules defined.

[srlabhe]

@Greg_Deckler ahhh got it