Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
My current calculated column, "Allowable" looks for blanks in certain areas and returns one thing if there are blanks, and another for everything else:
Solved! Go to Solution.
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]
)
Proud to be a Super User!
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 🙂
And here is a sample of the other table referenced in my "allowable" column
PatientInternalID | InsPrimaryInsuranceCompanyName |
122161 | Regence BCBS MedAdvantage |
385842 | OHP AllCare Health Plan CCO |
249147 |
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]
)
Proud to be a Super User!
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:
ChargeID | Date | BillID | ChargeCodeID | CPTModifier1Description | OrderingProviderID | PatientInternalID | TotalChargeAmount | % of Medicare Allowable | Medicare Allowable Chg | Allowable |
2582406 | 01/04/21 | 506805 | 139791 | Surgery | 278985 | 122161 | 1,375.38 | 100% | 1082.33 | 1,082.33 |
2600003 | 01/04/21 | 510320 | 139873 | Left upper lid | 374208 | 385842 | 304.35 | 77% | 258 | 198.66 |
2600002 | 01/04/21 | 510320 | 133858 | Left lower lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
2600001 | 01/04/21 | 510320 | 133858 | Left upper lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
2600000 | 01/04/21 | 510320 | 139874 | Left lower lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
2599999 | 01/04/21 | 510320 | 139874 | Left upper lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
2599998 | 01/04/21 | 510320 | 139905 | Left lower lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
2599997 | 01/04/21 | 510320 | 139905 | Left upper lid | 374208 | 385842 | 1,082.53 | 77% | 888.92 | 684.47 |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |