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

     

     

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.