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?
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?
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.