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
Hi, For some reason, every time I try to create a many-to many relation, I get this error message.
And when I link data through a bridge table, it doesn't link correctly in my report
Can you help me please?
Hi imanhassan,
Can you raise this as a separate issue and @ me in it? If you could include a screenshot of the relationship view and a screenshot of the two tables you're trying to relate that would help me help you!
Kind regards,
Jordan
Anonymous , sorry but I have no access right to create a new issue. 😞
imanhassan ah ok, well all the detail here you can. Send over that relationship view.
It's possible your bridge contains duplicates? Try removing all dups just to leave just unique values.
Anonymous well, my problem has 2 sides:
first side: Limitation of May 2020 version, as it doesn't have the many-to-many cardinality. Also, on the newer version, I have different preview options than the ones mentioned in the blog post.
second side: The original problem, for which I created a very simplified example file below:
My issue is that I have a report from the reporting tool with names, departments , date and workload data. I simplified it here to exclude the individual names and keep only departments.
What I need to do, is to add an additional fixed value to department C each month (Values are in 2nd table). And I need to display data by month.
But when I link the tables with month-year, the constant value for Dept C is repeated also for all other departments (see marked in yellow below).
When I link with department, only department C is displayed.
When I try to link tablew with month-year and department , I get the error mentioned before.
Is there a better way to add a constant monthly value, other than using the second table? (The first table I get from a tool, the second table I created manually).
So how can I add a monthly value to department C only, so that I can draw a monthly trend?