Count Open Cases

Hi All,

I don’t know what i am doing wrong here.

I have a property table that is linked to claim payment table. and claim table and payment has one to one relationship.

i want to count the number of open cases, close cases etc

In Year of account 18, i know there are 6 open claims but my calulation is counting all cases

Open Cases = CALCULATE(COUNT('Property'[Policy Number]), Claims[Claim Status] ="Open")

i drag policynumber from policy and claim status from claim as seen below using the filters in the screen shot

@alora101,

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.

  • Brian

Hi Brain,

Thanks very much.

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

@alora101,

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.

  • Brian

Hi Brain,

I have done it and still nothing works, I really don’t want to flatten the claims.

i dont know if i need to add date to the dax calculation because when i filter on the year, it brings correct result.

@alora101,

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?

Thanks.

  • Brian

Hi Brain,

it will be hard for me to mask this file as too many data in it.

let me summarize

  1. we normally have three separate files ( claims, marine and property)
  2. Both property and marine are risk level data
  3. The claim file contain policy numbers that they have made claims
  4. The Business manually get figures from the claim file and load put into either the property or marine file
  5. The claim has more than five line of businesses ( marine, property, car, aviation etc)
  6. 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
  7. and build cube
  8. 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.

Can i send the file to you privately and we continue to discuss the problems and the solution for others to learn?

@alora101,

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

  • Brian

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.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

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!

Hi Brain,

I have just put this together quickly.

Let me know what you think though i have flatten the claim and claimpayment tabletest_dna.xlsx (16.3 KB) testFile.pbix (101.1 KB)

@alora101,

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:


Now the measures to count claims by type work just as expected:

Count Open Claims = 

CALCULATE(
    COUNTROWS( Claims ),
    Claims[Claim Status] = "Open"
)

I hope this is helpful. Full solution file attached below.

Thanks Brian,

but what if i decided to split the claim like what i have in the beginning ?

Thanks

@alora101,

It’s hard to say conclusively, given that I can only see a snippet of what you had in your original Claims lookup table:

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.

  • Brian
1 Like

Hi Brain,
I will come up with the file shortly. The claims has too many columns that needs to be broken into dimension and fact.

Thanks for the great work you guys are doing

Hi Brain,

I figured it out, i applied the cross filter direction to both and it gives me the correct figures.

@alora101,

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.

  • Brian

Thanks Brian.

I will create bridge table

@alora101,

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.

  • Brian