Custom Slicer / How do I built this sort of usability

[Anonymous]

Hi,

I have an excel table 'Claim Data' I'm bringing in to Power BI.  The table has medical claim data.  The two fields that apply to my question here are [Plan Payment Amount] and [Claim Serial Number].  Each [Claim Serial Number] can populate numerous times in this table (meaning a customer could have gone to the same doctor for the same cold multiple times and it would have been logged with the same [Claim Serial Number] because it was for the same issue).  Each instance (row) of the [Claim Serial Number] is also populated with a [Plan Payment Amunt].  For example:

Claim Serial Number     Plan Payment Amount

0077                              $1.00

0077                              $3.00

0077                              $10.00

0050                              $150.00

0077                              $10.00

0030                              $25.00

With that in mind, I'd like to associate certain [Plan Payment Amount] totals for each [Claim Serial Number] as either a (High Cost Claimant), a (Mediam Cost Claimant) or neither.

For this example, lets pretend that a (High Cost Claimant) is any situation where a [Claim Serial Number]'s total [Plan Payment Amount] is greater than or equal to $150.00.  Let's also pretend that a (Medium Cost Claimant) is where a [Claim Serial Number]'s total [Plan Payment Amount] is greater than or equal to $25.00 but less than $150.00.  In any other situation (if the [Claim Serial Number]'s total [Plan Payment Amount] is less than $25.00)...it is neither a (High Cost Claimant) or {Medium Cost Claimant).

For this example, [Claim Serial Number] 0050 is the only (High Cost Claimant).  [Claim Serial Number] 0030 is the only (Medium Cost Claimant) while 0077 is neither a (High Cost Claimant) or (Medium Cost Claimant).

Currently, I have this created as a measure:

Claim Category Type =

VAR _Sum = SUM('Claim Data'[Plan Payment Amount])

RETURN

IF(_Sum >= 27000 , "High Cost Claim", If(_Sum >= 25, "Medium Cost Claim", "Low Cost Claim"))

While this works as intended...I now want to also create a slicer so I can influence my tabs/visualizatios.  I'd like to have a slicer where I can select (High Cost Claimant), (Medium Cost Claimant) or (Neither).    My question is, do I need to create three custom columns in Edit Query?  I'm not sure that would work because of how each row could potentially have the same [Claim Serial Number].  I'm sure all of this is possible but I'm not sure how.

Here is a picture of my relationships.  I already have a Date Table built so hat may complicate my situation further.  I also have several ncesary custom column already built in my [Claim Data] table so if the solution to my initial question is some sort of an append table I'm not sure how that would impact everything.

[westwrightj · Accepted solution]

Hey @Anonymous ,

   Thanks for reaching out. There are a few ways to go about what you are trying to do.

     For the solution I've written I've used the following example data

First we can create a new calculated column like the following

Categorize Claim Type =

var TheAmount = CALCULATE(SUM(Example_Data[Plan Payment Amount]), ALLEXCEPT(Example_Data, Example_Data[Claim Serial Number]))

return

IF(TheAmount >= 150, "High Cost Claimant",
IF(TheAmount >= 25 && TheAmount < 150, "Medium Cost Claimant",
"Neither High or Medium Cost"))

   That will change our example data to look like this

Now you can use that calculated column in the left hand filter pane or as a slicer for your data as you can see in the below snippets

Let me know if that solution works for your or if another response did. If not, let us know and we can keep noodling it over.

[Pragati11]

HI @Anonymous ,

The measure what you have already created (Claim Category Type), just create it as a calculated column rather than a measure.

Then use it as a slicer.

Thanks,

Pragati