Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have 2 tables Visit and Payment.
Visit
visit it
program
first bill insurance
customary charge
contractual charge
date first bill
date of service
Visit Id | First Bill Insurance | customary Charge | contractual | program | first bill | date of service |
100 | Aetna | 100 | 60 | acp | 1/1/2018 | 1/1/2018 |
100 | Aetna | 100 | 60 | acp | 1/1/2018 | 1/1/2018 |
200 | Fidelis | 200 | 160 | fed | 1/1/2018 | 1/1/2018 |
300 | Healthfirst | 300 | 260 | fed | 1/1/2018 | 1/1/2018 |
300 | Healthfirst | 300 | 260 | fed | 1/1/2018 | 1/1/2018 |
Payment
visit id
amount
activity type
insurance name
date - transaction
Visit Id | Payment Insurance | amount | activity type | date transaction | ||
100 | aetna | 0 | payment | 10/1/2018 | ||
100 | fidelis | 80 | payment | 10/5/2018 | ||
200 | fidelis | 150 | write-off | 10/1/2018 | ||
300 | healthfirst | 0 | payment | 10/1/2018 | ||
300 | aetna | 200 | write-off | 10/6/2018 |
I will filter on visit.date of service for calendar year of 2018
I want to create a column called Visit Balance which will subtract visit.customary charge - payment.amount
I want to create a column called "Type" which will:
- check each visit id and if there are more than one visit ids, then check the intial bill insurance and the insurance name from payment and if they are same then return 0,
- check the intial bill insurance and the insurance name from payment and if they are different then return 1, and then for all the 1s.
then for all the visit ids with 1 that we just created, the visit id with min date of transaction will return not collectable and for the the max date of transaction will return "collectable" and if there is only one visit id, then return"collectable"
This should be the end result
Visit Id | First Bill Insurance | Payment Insurance | customary Charge | contractual | amount | Balance Left | first bill | date transaction | status |
100 | Aetna | aetna | 100 | 60 | 0 | 100 | 1/1/2018 | 10/1/2018 | uncollectable |
100 | Aetna | fidelis | 100 | 60 | 80 | 20 | 1/1/2018 | 10/5/2018 | collectable |
200 | Fidelis | fidelis | 200 | 160 | 150 | 50 | 1/1/2018 | 10/1/2018 | colletable |
300 | Healthfirst | healthfirst | 300 | 260 | 0 | 300 | 1/1/2018 | 10/1/2018 | uncollectable |
300 | Healthfirst | aetna | 300 | 260 | 200 | 100 | 1/1/2018 | 10/6/2018 | collectable |
Solved! Go to Solution.
Hi @saanah2019 ,
1. To create M-M relationship between tables as below.
2.Create measures as below to the the excepted result.
Balance Left = CALCULATE(MAX(Visit[customary Charge]))- CALCULATE(MAX(Payment[ amount]))
status = VAR countro = COUNTROWS ( Visit ) VAR mindate = CALCULATE ( MIN ( 'Payment'[date transaction] ), ALLEXCEPT ( Payment, Payment[Visit Id] ) ) RETURN IF ( ISBLANK ( [Balance Left] ), BLANK (), IF ( countro = 1, "colletable", IF ( MAX ( Payment[date transaction] ) = mindate, "uncollectable", "collectable" ) ) )
Hi @saanah2019 ,
1. To create M-M relationship between tables as below.
2.Create measures as below to the the excepted result.
Balance Left = CALCULATE(MAX(Visit[customary Charge]))- CALCULATE(MAX(Payment[ amount]))
status = VAR countro = COUNTROWS ( Visit ) VAR mindate = CALCULATE ( MIN ( 'Payment'[date transaction] ), ALLEXCEPT ( Payment, Payment[Visit Id] ) ) RETURN IF ( ISBLANK ( [Balance Left] ), BLANK (), IF ( countro = 1, "colletable", IF ( MAX ( Payment[date transaction] ) = mindate, "uncollectable", "collectable" ) ) )
@v-frfei-msft hey, thanks for your help but the balance left is not coming out correct for me and as a result the status are all wrong.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |