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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Reine
Helper IV
Helper IV

Need help to return multiple calculations based on several different conditions

Hello - I have run into a problem that is beyond my knowledge and would appreciate any help you can give.

I have a table with data for surgeries performed per day.  With this data I am calulating total cost for each surgery, then using that data and some data from other tables to determine what their insurance will allow to be paid for that surgery. Currently I have a calculated column set up in my table with an IF statement that is working well for the simple conditions I needed.  But now I have been asked to add in some other conditions and I can't figure it out.

My table looks like this, the last 3 columns are calculated columns:

Reine_2-1626390708069.png

 

My current calculated column, "Allowable" looks for blanks in certain areas and returns one thing if there are blanks, and another for everything else:  

Allowable = IF(ISBLANK('report Charge'[Medicare Allowable Chg]),'report Charge'[TotalChargeAmount], IF(ISBLANK(RELATED('custom Patient'[InsPrimaryInsuranceCompanyName])),'report Charge'[TotalChargeAmount],'report Charge'[Medicare Allowable Chg]*'report Charge'[% of Medicare Allowable]))

It turns out that if there is more than one "ChargeCodeID" per "BillID", then insurance will pay the percentage in the "% of Medicare Allowable" column for ONLY the most expensive part of the surgery - the rest are paid at 50% of that.  So what I need to do now is add in another IF statement (or do something else entirely) to my "Allowable" column that not only looks at the current conditions but also checks if the "Bill ID" has more than one "ChargeCode ID", and if it does, find the one with the highest charge and multiple it by  "% of Medicare Allowable" and the rest of them at 50% of the percentage in the "% of Medicare Allowable" column. Often there will be several with the same Charge amount, and in that case it doesn't matter which it picks, it just has to be one of highest.
 
So in the above sample data, Bill 510320 has 8 "ChargeCodeID" rows - it should pick one of the charges of 1082.53 and kick back 77% of the "Medicare Allowable Chg" for it, and all the rest of the rows should kick back 50% of that 77%.  
 
Bill 506805 has only one ChargeCodeID, so it would be subject to the other conditions in my original If statements only.

I hope this makes sense, please let me know if further info is needed.  Thank you 🙂



 

 

 

1 ACCEPTED SOLUTION

@Reine,

 

Try this calculated column:

 

Allowable = 
SWITCH (
    TRUE (),
    // scenario 1
    ISBLANK ( 'report Charge'[Medicare Allowable Chg] )
        || ISBLANK ( RELATED ( 'custom Patient'[InsPrimaryInsuranceCompanyName] ) ), 'report Charge'[TotalChargeAmount],
    // scenario 2
    CALCULATE (
        COUNTROWS ( 'report Charge' ),
        ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] )
    ) > 1,
        VAR vMaxAmount =
            CALCULATE (
                MAX ( 'report Charge'[TotalChargeAmount] ),
                ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] )
            )
        VAR vMinChargeIDForMaxAmount =
            CALCULATE (
                MIN ( 'report Charge'[ChargeID] ),
                ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ),
                'report Charge'[TotalChargeAmount] = vMaxAmount
            )
        RETURN
            SWITCH (
                TRUE (),
                'report Charge'[ChargeID] = vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable],
                'report Charge'[ChargeID] <> vMinChargeIDForMaxAmount,
                    'report Charge'[TotalChargeAmount] * [% of Medicare Allowable] * .5
            ),
    // scenario 3
    'report Charge'[Medicare Allowable Chg] * 'report Charge'[% of Medicare Allowable]
)

 

DataInsights_0-1626472484837.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Reine
Helper IV
Helper IV

Oh my goodness - you are a life saver!  Thank you!  This works perfectly.  This is defintely over my head, though I do understand at least part if what is going on.  I guess I am going to need to research SWITCH.  Thanks again, I appreciate it very much 🙂

Reine
Helper IV
Helper IV

And here is a sample of the other table referenced in my "allowable" column

 

PatientInternalIDInsPrimaryInsuranceCompanyName
122161Regence BCBS MedAdvantage
385842OHP AllCare Health Plan CCO
249147 

@Reine,

 

Try this calculated column:

 

Allowable = 
SWITCH (
    TRUE (),
    // scenario 1
    ISBLANK ( 'report Charge'[Medicare Allowable Chg] )
        || ISBLANK ( RELATED ( 'custom Patient'[InsPrimaryInsuranceCompanyName] ) ), 'report Charge'[TotalChargeAmount],
    // scenario 2
    CALCULATE (
        COUNTROWS ( 'report Charge' ),
        ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] )
    ) > 1,
        VAR vMaxAmount =
            CALCULATE (
                MAX ( 'report Charge'[TotalChargeAmount] ),
                ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] )
            )
        VAR vMinChargeIDForMaxAmount =
            CALCULATE (
                MIN ( 'report Charge'[ChargeID] ),
                ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ),
                'report Charge'[TotalChargeAmount] = vMaxAmount
            )
        RETURN
            SWITCH (
                TRUE (),
                'report Charge'[ChargeID] = vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable],
                'report Charge'[ChargeID] <> vMinChargeIDForMaxAmount,
                    'report Charge'[TotalChargeAmount] * [% of Medicare Allowable] * .5
            ),
    // scenario 3
    'report Charge'[Medicare Allowable Chg] * 'report Charge'[% of Medicare Allowable]
)

 

DataInsights_0-1626472484837.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Reine
Helper IV
Helper IV

Here is the sample data pasted instead of a screen shot.  This is the 'report Charge' table. Let me know if I still haven't done this correctly:

ChargeIDDateBillIDChargeCodeIDCPTModifier1DescriptionOrderingProviderIDPatientInternalIDTotalChargeAmount% of Medicare AllowableMedicare Allowable ChgAllowable
258240601/04/21506805139791Surgery2789851221611,375.38100%1082.331,082.33
260000301/04/21510320139873Left upper lid374208385842304.3577%258198.66
260000201/04/21510320133858Left lower lid3742083858421,082.5377%888.92684.47
260000101/04/21510320133858Left upper lid3742083858421,082.5377%888.92684.47
260000001/04/21510320139874Left lower lid3742083858421,082.5377%888.92684.47
259999901/04/21510320139874Left upper lid3742083858421,082.5377%888.92684.47
259999801/04/21510320139905Left lower lid3742083858421,082.5377%888.92684.47
259999701/04/21510320139905Left upper lid3742083858421,082.5377%888.92684.47

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.