Hi all,
Currently having some data model issues that I'd love some help with.
In the below photo, I have a measure "Vol Set ADV" which comes from the "Claims Outcome" table. It can't identify the date of each entry, which is giving it the same repeated measure for every broker. Currently, the "date month" comes from the "Date" table and the "Main Account Name + FCA" comes from the "Claims Reported" table. I'm assuming I've set up the relationships horribly. In a previous and similar report I merged the qureies together, however I don't seem able to do this with Outcome and Reported here? I'd appreciate if someome could jump in and have a look, as I've struggled with this for a day or two now and can't figure it out!
*No sensitive info in file!*
https://www.dropbox.com/s/gzmpue0ys503lor/Claims%20Fraud%20League%20Table.pbix?dl=0
Kind regards,
Jordan
Solved! Go to Solution.
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?
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
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
105 | |
72 | |
68 | |
47 | |
47 |
User | Count |
---|---|
161 | |
85 | |
76 | |
68 | |
67 |