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

     

     

smpa01's avatar
smpa01
Community Champion
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