How to use inactive relationship for cascade slicers

[KylieF]

Hi there, 

I want to make cascade slicers but the relationship of the two columns is inactive because of ambiguity introduced by another table. How to enable the cascade slicers?

Situation explained: I have 3 tables:

1. DimCompany

2. DimCampus

3. DimStudent

My data model is:

The relationship between DimCampus[CompanyKey] and DimCompany[CompanyKey] is inactive because both [CompanyKey] and [CampusKey] are present in DimStudent.

The report is:

I want the two slicers CompanyName and CampusName to be cascade to each other. I'd appreciate anyone provide hints or solutions!

[v-yilong-msft · Accepted solution]

Hi @KylieF ,

I create three tables as you mentioned.

Then I create a new table and here is the DAX code.

BridgeTable = 
DISTINCT(
    UNION(
        SELECTCOLUMNS(DimStudent, "CompanyKey", DimStudent[CompanyKey], "CampusKey", DimStudent[CompusKey]),
        SELECTCOLUMNS(DimCampus, "CompanyKey", DimCampus[CompanyKey], "CampusKey", DimCampus[CompusKey])
    )
)

I also create two relationships between them.

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KylieF]

What I've learned is that setting it to bidirection is not a good practice because it might yield undesired results. We should always avoid using it. Any other solutions? 

[aduguid]

You could create calculated columns in your DimStudent table for both CompanyName and CampusName and then use those in your slicers.

Company Name = RELATED('DimCompany'[CompanyName]) 

[aduguid]

If you change the cross-filter direction of both of the active relationships from "Single" to "Both", that should work.