Data model help

[Anonymous]

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 

[Greg_Deckler · Accepted solution]

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.

[imanhassan]

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?

[Anonymous]

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  

[imanhassan]

@Anonymous , sorry but  I have no access right to create a new issue.  😞

[Anonymous]

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

[imanhassan]

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

[Greg_Deckler]

The man that the data model is a mess! 🙂

All I see is that it's a many-to-many between the two tables in question. That could definitely cause some cattle. Maybe he'll try inserting a bridge table.

[Greg_Deckler · Accepted solution]

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.

[Anonymous · Accepted 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 

[Greg_Deckler]

Oh yeah! That is waaaaaaaaayyyyyy better!!