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 Status Column" (in yellow in my picture).

Whar formula sould I use in Power Query to do that?

 

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

     

     

3 Replies

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

    Hi mglomb ,

     

    You can use this formula in PowerQuery:

    Actual status =

    if [Delivered] = 1 then "Delivered" else
    if [Fix] = 1 then "Fixed" else
    if [Received] = 1 then "Received" else null

     

    If I answered your question, please mark it as a solution to help other members find it more quickly.

  • 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.

     

     

  • 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