Forum Discussion
Post PatronMany to many crossfiltering none
Hi guys;
I have 3 tables that are linked 'many to many' to each other. These are the 3 tables:
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?
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.
6 Replies
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.
MiKeZZaHi Anonymous, 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....
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.