cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ArulselvanD07 Helper I
Helper I

Cross filtering between two facts and common dimension tables

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Cross filtering between two facts and common dimension tables

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Cross filtering between two facts and common dimension tables

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

ArulselvanD07 Helper I
Helper I

Re: Cross filtering between two facts and common dimension tables

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.

Super User IV
Super User IV

Re: Cross filtering between two facts and common dimension tables

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ArulselvanD07 Helper I
Helper I

Re: Cross filtering between two facts and common dimension tables

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? 

Super User IV
Super User IV

Re: Cross filtering between two facts and common dimension tables

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ArulselvanD07 Helper I
Helper I

Re: Cross filtering between two facts and common dimension tables

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors