help creating conditional statement using 2 tables

[saanah2019]

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

[v-frfei-msft · Accepted 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"
            )
        )
    )

[saanah2019]

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