Cross filtering between two facts and common dimension tables

[ArulselvanD07]

I have the following scenario.

I have two fact tables [FactA and FactB] and 9 dimension tables. FactA and FactB contains those 9 dimensions and their own measures. I have made relationships between fact tables and dimensions. My relations look like FactA --> 9 dimension tables <-- FactB. I am trying to pull the measures from both fact tables into a table visualization and trying to filter the results using common dimensions. It works for the measure pulled from one fact table and not for the other fact table. I tried to set the cross filter direction to both on all the relationships between common dimensions and facts. I was able to set the cross filter for one dimension table, when I am trying to set the same for remaining dimension tables, I am getting an warning that Power BI desktop allows only one filtering path between tables in a data model.

Also I tried to see if the cross filtering is working fine for the one dimension that I enabled cross filtering by pulling that dimension key and measures from both facts. But it didn't change anything.

Is there a way I can pull measures from both facts and have it filtered by common dimension values? Please share your thoughts and suggestion. Thanks in advance.

[Ashish_Mathur · Accepted solution]

Hi,

I believe the best practise would be to create 9 tables (which are the common columns, dimensions in your parlance) of one column each (with only unique enties in that one column) and connect both your fact tables to the nine tables.  When building visuals, drag the dimensions from the 9 tables.

Hope this helps.

[ArulselvanD07]

Hi Ashish,

Yes, those 9 dimensions are physical tables. I have reworded the scenario now.

As you mentioned my model is connected like this

FactA --> 9 dimension tables <-- FactB

I tried to pull one measure from Fact A, one measure form FactB and a dimension from one of the 9 dimension table. But it is not working as expected.

[Ashish_Mathur]

Hi,

That should work.  Ensure that:

1. The visuals carry columns from the 9 dimension tables, wherever possible; and
2. the measures you have written refer to the dimension tables

If it still does not work, then please share the link from where i can download your PBI file.  Tell me exactly where the problem is and also let me know the exact figure you are expecting.

[ArulselvanD07]

Ashish,

Could you please elaborate on the second bullet point "the measures you have written refer to the dimension tables". I have created measures on top of Fact tables, and those measures does not involve the common dimension columns. Those measures are doing aggreations, basically the count of a particular column in both Fact tables.

Am I missing something here? 

[Ashish_Mathur]

Hi,

If the meaure are very basic aggeate functions, then you may ignoe my statement.  I will need to see your file.  Please very clearly indicate in that file where the problem is and also show the correct result.

[ArulselvanD07]

@Ashish_Mathur I have found the issue, there was one dimension which was not connected to the FactB and in the visuals I pulled that dimension column from FactA. So the numbers were not matching. I have modified the both. Now both of my facts are connected to all the dimensions, cross filtering is enabled between one dimension and a fact. In my visuals tab, I pulled the dimension columns from the dimension tables and the measures from Fact tables. It is working fine now, Also I have hidden all dimension columns on both Fact tables, just to avoid confusions. 

Thanks for your suggestions!