Many to many crossfiltering none

[MiKeZZa]

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?

[Anonymous]

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.

[MiKeZZa]

Hi @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....

[Anonymous]

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.

[MiKeZZa]

Hi @Anonymous 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.

[Anonymous · Accepted solution]

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

[MiKeZZa]

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