I strongly suspect the problem has to do with the bidirectional relationship between Claims and Claims Payment. Try adjusting the properties of that relationship to make it one-to-many only going in one direction (i.e., flowing down from the lookup table to the fact table). If your Claim Key is a unique identifier, it should allow you to make this change. If it doesn’t, it means there’s a many to many relationship that will need to be addressed in a different way.
It would definitely help to have the PBIX to be able to definitively determine the source of the problem, but give the above a try and see if that works.
these are excel files that i transform into tables in database.
The claim file i did broke it down into two part namely claims ( the string values) and claim payment ( numeric values ) .
At the moment, they receive files and for you to know which property makes a claim, you need to open claim files and check so i am taking that manual process away.
So claim and claim payment will be one to one unless i have to make it one single table
One to one is OK, but still try to change the relationship to one to many. Power BI doesn’t care how many is on the “many” side of one to many. The key here is to get your model flowing in the right direction and get rid of that bidirectional relationship.
Hmmm…OK, at this point to make any further progress in coming up with a solution, I would really need to see the full PBIX file. Would you be able to upload that, masking any confidential information?
it will be hard for me to mask this file as too many data in it.
let me summarize
we normally have three separate files ( claims, marine and property)
Both property and marine are risk level data
The claim file contain policy numbers that they have made claims
The Business manually get figures from the claim file and load put into either the property or marine file
The claim has more than five line of businesses ( marine, property, car, aviation etc)
my job is to load them into a database and data warehouse by creating a unique key ( combination of Address, policy, state and location id) these unique value is the relationship to Marine and property
and build cube
i have created based on my knowledge and wanting to split each file into various dimension thats is where i am.
Do you reckon i de-normalize the claim and make it same way i got the file but the issue is using the claim as fact table, there is many dimensional fields in it.
@sam.mckay’s rules for the forum are that all business must be conducted publicly. However, I think the only tables we need to worry about here are Claims, Claims Payment and Dates. Can you put together a simple model with those three tables (mirroring your actual data structure in terms of tables and fields) and just some nonsense data in the fields that contain sensitive/confidential information?
That way, we can work out the DAX on the nonsense data, but if the field names are the same, once we’ve got it working you can just copy the measures into your actual report and you should be good to go.
Hi @alora101, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. Here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hi @alora101 , we’ve noticed that no response has been received from you since the 2nd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Thanks – this makes it much easier to provide a specific solution. As is often the case when the DAX looks right, but is still producing weird results, it’s the data model that’s the culprit. I took your data, eliminated the duplicates in the Property table and then was able to set the relationship to Claims as one-to-many, single direction and added a date table and marked it as such:
However, from what I can see, a lot of that looks like it belongs in a fact table, not a dimension /lookup table. I find this a very helpful way to think of the decision as to what belongs where in the data model:
Dimension tables represent “things” (e.g., customers, locations, products, etc.), the aspects of which remain fairly stable over time
Fact tables represent “actions” (e.g., claims being processed, sales being made, persons being hired), the aspects of which change constantly
Most of what I can see in your original Claims table to me represents “actions” and is thus more suited to a fact table. However, if you can provide the full list of fields in that Claims table, it will help me better understand whether there’s a subset that would be suitable for crafting a dimension table - perhaps a Policies dimension table, and a Claims Processed fact table?
It’s also valuable to think of what dimensions you want to slice your fact table on, and what reports you want to create, in order to ensure that your data model supports your end goals.
So, that’s probably a less conclusive answer then you were hoping for, but hopefully helpful nonetheless. With a bit more information, we can craft a more specific data model.
While it may give you correct numbers in this particular instance, I would strongly caution against setting up your model this way. The use of bidirectional relationships can cause ambiguity in your model, and potential inaccuracies in other calculations. Here’s a great article that explains the problem in depth:
There are lots of other techniques (e.g., general model restructuring, bridge tables, inactive relationships, TREATAS, etc.) that can be used in place of active bidirectional relationships to avoid this problem.
If you’d like to continue working this issue through the forum, please start another topic, since the original question has been solved. Thanks.
That should work, and used to be my preferred approach as well, but before you do I’d suggest watching this video:
TREATAS was one of the more difficult functions for me to wrap my head around, and while I’d watched the other videos on the topic, it never really “clicked” for me until this one. However, after watching this one, it became a function I use all the time and I don’t think I’ve created a bridge table since. There’s no right or wrong between these approaches, but I like how TREATAS keeps my data model cleaner.
