Forum Discussion
Data model help
- Man that data model is kind of a mess! 🙂
Only thing I see is that it is a many-to-many between the two tables in question. That could definitely cause some wonkiness. Perhaps try inserting a bridge table. Hi Greg_Deckler ,
Thank you for the suggestion! I had a good look into it and found an excellent blog post that explained what a bridge table is, relationships and cardinality!
https://www.seerinteractive.com/blog/join-many-many-power-bi/
I have now transformed my messy PBI auto assigned datamodel into something entirely functional with a bridge table (that I already had and didn't need to create!). The workings I'll share below to hopefully provide some clarity to anyone else who stumbles across this...
In the below photo, you can see the issue I had, an asbolute mess with lots of many to many reltionships filtering eachother. Datamodel before - auto assigned by PBI
My datamodel after, which works seamlessly! I've now have two bridge tables, one is a self created datetable using
DateTable 2019 = CALENDAR(DATE(2019,01,01),DATE(2019,12,31).
I have set the relationship from the date table to the Claims Reported, Claims Retained and Claims Outcome tables with a "Many to One" cardinality, with the cross filter diree=ction as single. This has prevented rows of data going missing as the date table contains a complete list of days in 2019.
I have also used the "Brokers" which was an existing tab in my data, placing this centrally using "Broker" to set the relationship to "Claims Retained", "Claims Reported" and "Claims Outcome". The reltionships of these has to be set to Many to Many, however I can set the Cross filter direction to "Single, (Brokers filters Claims X)". Setting this uses the Broker list as a reference point, preventing me from losing any data. Usin gthe combo of the "Broker" table and my "DateTable" allows me to compare everything against eachother. It also looks a lot cleaner as you can see below!
Data Model after - manually done
Kind regards,
Jordan
Only thing I see is that it is a many-to-many between the two tables in question. That could definitely cause some wonkiness. Perhaps try inserting a bridge table.
Hi Greg_Deckler ,
Thank you for the suggestion! I had a good look into it and found an excellent blog post that explained what a bridge table is, relationships and cardinality!
https://www.seerinteractive.com/blog/join-many-many-power-bi/
I have now transformed my messy PBI auto assigned datamodel into something entirely functional with a bridge table (that I already had and didn't need to create!). The workings I'll share below to hopefully provide some clarity to anyone else who stumbles across this...
In the below photo, you can see the issue I had, an asbolute mess with lots of many to many reltionships filtering eachother. Datamodel before - auto assigned by PBI
My datamodel after, which works seamlessly! I've now have two bridge tables, one is a self created datetable using
DateTable 2019 = CALENDAR(DATE(2019,01,01),DATE(2019,12,31).
I have set the relationship from the date table to the Claims Reported, Claims Retained and Claims Outcome tables with a "Many to One" cardinality, with the cross filter diree=ction as single. This has prevented rows of data going missing as the date table contains a complete list of days in 2019.
I have also used the "Brokers" which was an existing tab in my data, placing this centrally using "Broker" to set the relationship to "Claims Retained", "Claims Reported" and "Claims Outcome". The reltionships of these has to be set to Many to Many, however I can set the Cross filter direction to "Single, (Brokers filters Claims X)". Setting this uses the Broker list as a reference point, preventing me from losing any data. Usin gthe combo of the "Broker" table and my "DateTable" allows me to compare everything against eachother. It also looks a lot cleaner as you can see below!
Data Model after - manually done
Kind regards,
Jordan
- Oh yeah! That is waaaaaaaaayyyyyy better!!