cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MiKeZZa Helper V
Helper V

Many to many crossfiltering none

Hi guys;

 

I have 3 tables that are linked 'many to many' to each other. These are the 3 tables:

 

fact_tables.png

 

They are linked by generalkey. But I was expecting an option to generate a 'cross join'.... So I wanted these output:


Test 1 - Test 4 - Test 7

Test 1 - Test 4 - Test 8

Test 1 - Test 4 - Test 9

Test 1 - Test 5 - Test 7

Test 1 - Test 5 - Test 8

Test 1 - Test 5 - Test 9

Test 1 - Test 6 - Test 7

Test 1 - Test 6 - Test 8

Test 1 - Test 6 - Test 9

Test 2 - Test 4 - Test 7

Test 2 - Test 4 - Test 8

Test 2 - Test 4 - Test 9

Test 2 - Test 5 - Test 7

Test 2 - Test 5 - Test 8

Test 2 - Test 5 - Test 9

Test 2 - Test 6 - Test 7

Test 2 - Test 6 - Test 8

Test 2 - Test 6 - Test 9

Test 3 - Test 4 - Test 7

Test 3 - Test 4 - Test 8

Test 3 - Test 4 - Test 9

Test 3 - Test 5 - Test 7

Test 3 - Test 5 - Test 8

Test 3 - Test 5 - Test 9

Test 3 - Test 6 - Test 7

Test 3 - Test 6 - Test 8

Test 3 - Test 6 - Test 9

 

But what I get is only the data that exists in all tables... So:

 

Test 3 - Test 5 - Test 7

 

Is there a common workaround or an other option to resolve this?

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Community Champion
Community Champion

Re: Many to many crossfiltering none

@MiKeZZa  By linking multiple fact tables to the same Date Dimension, you can do exactly what you have described.

 

What you will end up is not really a star schema, but conceptually each FACT table is still a star schema.  Its not really a snowflake schema either as you can't cascading outwards.  I personally refer to it as a sandwich schema, because i put the Dimension tables on the outside (bread) and the fact tables in the centre (meat).  I'm sure someone has a more technical name.

 

From here you can easily place non-related data on common axis and show results.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

6 REPLIES 6
Ross73312 Community Champion
Community Champion

Re: Many to many crossfiltering none

As a general rule, if your solution involves a many to many relationship you have likely made a modelling mistake.  Power BI recently added the feature to allow many to many, but this needs to be used with the most extreme caution.

 

Instead, i recommend creatining a single dimension table that will hold 1 row per each value of the information that makes up your key and any properties of that Key.  For example, if that key was a Client ID. Then each row is essentially client information, so you might hold their Client Name, Phone Number etc.

 

Here you need to link each table to the Dimension table.  In your reports, use the dimension table for Slicers and Groupings.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


MiKeZZa Helper V
Helper V

Re: Many to many crossfiltering none

Hi @Ross73312, I understand totally what you say. But we want to service a 'power user' with at least 10 'fact tables' with a value per day. Lets say:

 

  • Sales per day
  • Income per day
  • Savings per day
  • ......
  • ......
  • ......

Our goal is to let this poweruser do some analysis on this dataset (Analyze in Excel) and then I also want to see the income per day. Even unless there is a sale or there are savings.... Of course we can trick this with a 0 for every combination in every table, but that's not the way I think....

Ross73312 Community Champion
Community Champion

Re: Many to many crossfiltering none

What you are describing is a pretty standard use for Power BI.  Typically you will have a Date Table as a dimension, thus you can do the per day and align the disparate tables in a single visual if required.

 

Building the model to hand off to a Power User is also a pretty standard use case for Power BI.  As data modellers its important we provide a model that works.  Data integrity will compromised if you have too many bi directional lines in your model.  Many to Many relationships, if not used as a complete last resort, will also destroy your data integrity.

 

If your model is poor, your power user will likely produce reports that show incorrect information.  As data modellers, its our job to save Power Users from themselves.

 

Work out what your different dimension tables are (Customer table, Date Table, Employee Table, etc).  Make sure your data is imported in a manner that is denormalised.  Create your table relationships such that your FACT tables are connected to your dimension tables.  Share your dimension tables with your FACT tables.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


MiKeZZa Helper V
Helper V

Re: Many to many crossfiltering none

Hi @Ross73312 thank you for your answer. To be honoust; I'm really familair with things like starschema's and other DWH-principles. So your story is really common to me. But for Excel purposes we want to give people the ability to pick one date and see many, NOT RELATED, facts that were 'active' this day. It can be 0, 1 or more facts in 1 table on 1 day.

 

So this is not possible in a normal starschema.

Ross73312 Community Champion
Community Champion

Re: Many to many crossfiltering none

@MiKeZZa  By linking multiple fact tables to the same Date Dimension, you can do exactly what you have described.

 

What you will end up is not really a star schema, but conceptually each FACT table is still a star schema.  Its not really a snowflake schema either as you can't cascading outwards.  I personally refer to it as a sandwich schema, because i put the Dimension tables on the outside (bread) and the fact tables in the centre (meat).  I'm sure someone has a more technical name.

 

From here you can easily place non-related data on common axis and show results.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

MiKeZZa Helper V
Helper V

Re: Many to many crossfiltering none

Yes; you are alright. The solution is not in making a complex full join, but by just don't making a relation between the facts, but only between fact-dim. That's clear. Been there, done that, but haven't realised it when the current situation changed (were already was a full join).

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.

Top Solution Authors
Top Kudoed Authors