cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JM_nxgn Regular Visitor
Regular Visitor

Table Relations Ambiguity

Hello, I've recreated the scenario that I'm currently experiencing, hopefully someone can give me some ideas on how I can fix this issue.

 

I have two fact tables which share many dimension tables, for simplicity sake I've only picked a few columns and a few dimensions to work with. The relationship view can be seen below:

RelationshipView.PNG

 

So i have picked the relations to be both directions, because I'm using report level filtering and need the filters to behave accordingly.

My current predicament is that it is forcing me to set the relationship between 'ReturnedGoods'[ProductId] and 'Dim_Product'[ProductId] to inactive, whereas I need it to be active. 

I have read up on the relationships and what's causing this. I sort of understand the issue, just not sure how I can get around it.

 

Thanks in advance.

 

 

4 REPLIES 4
Highlighted
Geradav Regular Visitor
Regular Visitor

Re: Table Relations Ambiguity

Hi @JM_nxgn 

 

Not sure to understand what is the problem you are seeking assistance with. Is it with the right DAX statement to create a measure or calculated column?

You have mentioned that you have purposedly created a bi-directional cross-filtering relationship (being aware of the consequences I guess) and that you are aware of the USERELATIONSHIP() function. But, what are you trying to achieve exactly?

 

Regards

David

JM_nxgn Regular Visitor
Regular Visitor

Re: Table Relations Ambiguity

Hello @Geradav 

 

So i'm actually not sure what the consequences of having the relationship being inactive is, but I assume it will be affecting the filtering. 

I'm actually not sure of the use of the USERRELATIONSHIP() function or if it pertains to my issue at all. 

Basically I have filters such as Product and Client (which I use from the Dim tables). 

I have visuals within each page that contains information about the SoldGoods and ReturnedGoods. I just want to ensure that when I filter the items in the Dim_Tables, that it will filter accordingly on the entire report and consequentially on all visuals. 

I assume if the relationship is 'inactive' as in the case mentioned above, the filtering will not work correctly. Am I wrong in saying that?

Community Support Team
Community Support Team

Re: Table Relations Ambiguity

@JM_nxgn 

 

If necessary, you may apply virtual relationship in DAX formula.

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JM_nxgn Regular Visitor
Regular Visitor

Re: Table Relations Ambiguity

@v-chuncz-msft 

 

Hello, sorry it has taken me a while to reply. 

 

So I understand how virtual relationships in DAX formula will help me for creating measures across various tables without having a physical relationship in existant, however I believe my situation is a bit different. 

 

Let me try provide more information on top of the original question I had posted, I don't believe I'm being very clear with my question.

 

My current situation is that I have one fact table which has physical relationships with many dimension tables (in a star schema layout). I have selected all relationships to be active AND to filter BOTH directions. 

The reason i require bi-directional filtering is because I'm using report-level filters in my report and require that if for example 'Product' is filtered on, then only the 'Customers' pertaining to the product being filtered on, appear in the filter selection (hope I'm clear with this).

I'm not sure if there is a more effective method of doing this without using relationships in BOTH directions, if there is please let me know.

 

My current situation is that I have additional fact tables that I need to add in and relate to the dimension tables. I want all these fact tables to be able to share the same filters and thus dimensions.

 

The issue I'm running into is that the moment I add another fact table and try to create physical relationships between the new fact table and existing dimension tables, I get an error regarding ambiguity OR I have to set the relationship to INACTIVE which doesn't help my situation.

 

I've considered combining the various fact tables into one table, but they do not really share common columns at all.

 

Thanks in advance!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,646)