Forum Discussion

saanah2019's avatar
saanah2019
Icon for Helper II rankHelper II
7 years ago
Solved

new column value based on conditions

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 idinsurance datestatus
100aetna1/1/2018not paid
100fidelis1/2/2018paid
101fidelis1/3/2018paid
101fidelis1/4/2018paid
102cigna1/5/2018paid
103healthfirst1/6/2018not paid
103cigna1/7/2018paid
103cigna1/8/2018paid
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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"
            )
        )
    )

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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"
            )
        )
    )