Table Relations Ambiguity

[JM_nxgn]

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:

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.

[Geradav]

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]

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?

[v-chuncz-msft]

@JM_nxgn 

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

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

[JM_nxgn]

@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!