Forum Discussion

lizman1111's avatar
lizman1111
New Member
6 years ago
Solved

Help w/ Calculated Column Formula

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

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

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

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      lizman1111
      New Member
      6 years ago

      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.