Help w/ Calculated Column Formula

[lizman1111]

Hi all,

I'm hoping someone can help me with writing a formula for a calculated column.  I work with patient data and would like to have a column in a table that lists the 1st site of service for each patient (meaning where they were discharged to after surgery).  Here's a simple rendering of the current table:

You can see the same Case ID is listed on mutliple rows for each event order.  What I would like to add is a column for 1st site of service (event order of 1) that returns the patient's 1st Facility Type for all rows of case ID's.  See example below:

I would greatly appreciate your help with this!

Liz

[jdbuchanan71 · Accepted solution]

@lizman1111 

Give this a try as your calculated column.

1st Site of Service = 
VAR _EventOrder =
    CALCULATE (
        MIN ( 'Table'[Event_Order] ),
        ALLEXCEPT ( 'Table', 'Table'[Case_ID] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Facility_Type] ),
        'Table'[Event_Order] = _EventOrder,
        ALLEXCEPT ( 'Table', 'Table'[Case_ID] )
    )

If your first Event_Order is always 1 you could use.

1st Site of Service = 
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Facility_Type] ),
        'Table'[Event_Order] = 1,
        ALLEXCEPT ( 'Table', 'Table'[Case_ID] )
    )

[lizman1111]

@jdbuchanan71 Yes!  The 2nd formula worked because the 1st site of service is always 1.  It can also be blank so I added an "IF(ISBLANK(..." statement.  Thank you!  I am researching the ALLEXCEPT function now...very helpful.