Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saanah2019
Helper II
Helper II

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
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

View solution in original post

1 REPLY 1
AlB
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.