Forum Discussion

mglomb's avatar
mglomb
Frequent Visitor
4 years ago
Solved

Get Latest status from multiple columns

Considering that I have a table as per below picture, with case number and count of status of each phase of a process. I want to check each column and bring the name of the status to a "Latest Statu...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    mglomb  it is definitely possible if you have Date as values for Recived, Fixed, Delivered

     

    Measure =
VAR _f1 =
    MAX ( 'Table'[Fixed] )
VAR _f2 =
    MAX ( 'Table'[Received] )
VAR _f3 =
    MAX ( 'Table'[Delivered] )
VAR _f4 =
    MAX ( MAX ( _f1, _f2 ), _f3 )
VAR _f6 =
    MAX ( 'Table'[Case] )
VAR _t =
    UNION (
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Fixed] ),
            "Attribute", "Fixed"
        ),
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Delivered] ),
            "Attribute", "Delivered"
        ),
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Received] ),
            "Attribute", "Received"
        )
    )
RETURN
    MAXX ( FILTER ( _t, [Case] = _f6 && [Date] = _f4 ), [Attribute] )

     

     

    pbix is attached

     

     

jdbuchanan71's avatar
jdbuchanan71
Super User
4 years ago

You can add a custom column like this.

 

= Table.AddColumn(#"Changed Type", "Actual Status", each if [Delievered] = 1 then "Delivered" else 
if [Fix] = 1 then "Fixed" else 
if [Received] = 1 then "Received" else "Other")

You can do this with the conditional column tool as well.