Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need help with LOOKUPVALUE

Hi everyone! I have a table (Table1) with 3 columns:

- ID

- Status

- Status Change Time

 

I need to create another table (Table2) that contains distinct IDs and last Status of each ID from Table1.

 

To achieve this I used the formula:

  

Table2 = SUMMARIZE('Table1';Table1[ID]; "Last Status"; LOOKUPVALUE('Table1'[Status]; Table1[Status Change Time]; MAX('Table1'[Status Change Time])))

 

 But it only works if Last Status = 1 in all rows. If at least one row has Last Status = 0, Power BI returns an error.

 

Could you help me to solve the problem and understand why it happens? Thanks!

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Anonymous

     

    Hi, try with this

     

    Table =
SUMMARIZECOLUMNS (
    Table3[ID],
    "MaxChangetime", CALCULATE ( MAX ( Table3[Status Change Time] ) ),
    "Status"; CALCULATE (
        VALUES ( Table3[Status] ),
        FILTER (
            Table3,
            Table3[Status Change Time] = MAX ( Table3[Status Change Time] )
        )
    )
)

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Anonymous

     

    Hi, try with this

     

    Table =
SUMMARIZECOLUMNS (
    Table3[ID],
    "MaxChangetime", CALCULATE ( MAX ( Table3[Status Change Time] ) ),
    "Status"; CALCULATE (
        VALUES ( Table3[Status] ),
        FILTER (
            Table3,
            Table3[Status Change Time] = MAX ( Table3[Status Change Time] )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks so much!