Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MiKeZZa
Post Patron
Post Patron

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
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

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
Not applicable

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.

 

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
Not applicable

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors