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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Data model help

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    

 

image.png

 

Kind regards,

 

Jordan 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Anonymous
Not applicable

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 PBIDatamodel 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 doneData Model after - manually done

 

Kind regards,

 

Jordan 

View solution in original post

9 REPLIES 9
imanhassan
Frequent Visitor

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

 

imanhassan_0-1599635246787.png

Can you help me please?

Anonymous
Not applicable

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

 

 

 

Anonymous
Not applicable

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

 

imanhassan_0-1599645023849.png

 

imanhassan_1-1599645348599.png

 

Greg_Deckler
Super User
Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 PBIDatamodel 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 doneData Model after - manually done

 

Kind regards,

 

Jordan 

Oh yeah! That is waaaaaaaaayyyyyy better!!

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.