Forum Discussion

JM_nxgn's avatar
JM_nxgn
Icon for Helper I rankHelper I
7 years ago

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,...
Need Help
JM_nxgn's avatar
JM_nxgn
Icon for Helper I rankHelper I
7 years ago

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?

  • JM_nxgn's avatar
    JM_nxgn
    Icon for Helper I rankHelper I
    7 years ago

    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!