cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arunaramana
Helper III
Helper III

why a single-only existing relation between fact-table & dim-table is inactive --AdventureWorksDW

Hello There,

I have a situation -- I noticed that the only exisiting-single relation between the (Fact-table) FactResellerSales and DimReseller (dimension table) is inactive. I happen to notice it as I am unable to filter FactResellerSales -- total-sales by a Reseller-column in DimResller-table -- as I get the same total across all the Resellers in the row.

 

My concern is, what could the scenarios a relation could be inactive other than when there are multiple-relations between a given fact-table and dimension-table? 

 

Would anyone please help me understand the scenario? Please find the screenshot of the model. Thank you for giving your valuable time.

 

Active-Inactive-Relations.png

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

You can't have multiple filter paths from one table to another without creating ambiguity.

 

Since you have a path DimSalesTerritory --> DimEmployee --> FactResellerSales, you can't also have an active relationship path DimSalesTerritory --> DimGeography --> DimReseller -- >FactResellersSales.

 

This article may help:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

 

View solution in original post

Burningsuit
Solution Sage
Solution Sage

Hi @arunaramana 

This relationship is inactive because you already have a relationship to FactResellerSales, through DimGeography to DimSalesTerrritory to DimEmployee. You can't have two active relationships to the same table, no matter how many intermediate tables it goes through.

You'll see you also have an inactive relationship from DimSalesTerritory to FactResellerSales because it also goes through DimEmployee.  

You can elect to make the most frequent relationship active, then use the DAX USERELATIONSHIP function to make the other relationship active for measures that need it.

Hope this helps

Stuart  

View solution in original post

4 REPLIES 4
Burningsuit
Solution Sage
Solution Sage

Hi @arunaramana 

This relationship is inactive because you already have a relationship to FactResellerSales, through DimGeography to DimSalesTerrritory to DimEmployee. You can't have two active relationships to the same table, no matter how many intermediate tables it goes through.

You'll see you also have an inactive relationship from DimSalesTerritory to FactResellerSales because it also goes through DimEmployee.  

You can elect to make the most frequent relationship active, then use the DAX USERELATIONSHIP function to make the other relationship active for measures that need it.

Hope this helps

Stuart  

View solution in original post

Thanks for your support, Staurt!

AlexisOlson
Super User
Super User

You can't have multiple filter paths from one table to another without creating ambiguity.

 

Since you have a path DimSalesTerritory --> DimEmployee --> FactResellerSales, you can't also have an active relationship path DimSalesTerritory --> DimGeography --> DimReseller -- >FactResellersSales.

 

This article may help:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

 

View solution in original post

Thanks, Alexis... 

I remember the point you explained in the response; I think I overlooked the relationship path. Thank you agian!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors