new column value based on conditions

[saanah2019]

Hi I have a very basic question that I am not able to find the answer to here.
So I will have one table that i need to look at and it will have a specific id, then the insurance name and a date of transaction for that visit id.
I want to create a new column which looks at the visit ids and makes sure that it is looking at the visit id and if they are the same,
- 1. then it checks the insurance names, if they are the same, both are called "Paid" in new column,
-2. If the insurance names are different then the one with the 'lower date' is 'not paid' and higher date is 'paid'

visit id	insurance	date	status
100	aetna	1/1/2018	not paid
100	fidelis	1/2/2018	paid
101	fidelis	1/3/2018	paid
101	fidelis	1/4/2018	paid
102	cigna	1/5/2018	paid
103	healthfirst	1/6/2018	not paid
103	cigna	1/7/2018	paid
103	cigna	1/8/2018	paid

[AlB · Accepted solution]

Hi @saanah2019,

Try this for your calculated column, where Table1 is the table you show:

NewColumn_Status =
VAR _NumberOfInsurers =
    CALCULATE (
        DISTINCTCOUNT ( Table1[insurance ] );
        ALLEXCEPT ( Table1; Table1[visit id] )
    )
RETURN
    IF (
        _NumberOfInsurers = 1;
        "paid";
        IF (
            _NumberOfInsurers > 1;
            IF (
                CALCULATE ( MIN ( Table1[date] ); ALLEXCEPT ( Table1; Table1[visit id] ) ) = Table1[date];
                "not paid";
                "paid"
            )
        )
    )