Forum Discussion

dostdahl's avatar
dostdahl
Icon for Helper I rankHelper I
4 years ago
Solved

How to show status updates in same row in Table

Hello,   I'm running into an issue with capturing a current status/date and a previous status/date and being able to show it in one row in a Table.   The dataset has a unique ID associated to eac...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi dostdahl ,

     

    I think you can create a measure and add this measure into the visual level filter of this table visual.

    Filter = 
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ),
    CALCULATE ( MAX ( 'Table'[Date From] ) ),
    ,
    DESC,
    DENSE
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • tamerj1's avatar
    tamerj1
    4 years ago

    @dostdahl 

    Here is the final updated solution

     

    Previous Status =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
VAR PreviousDate =
    MAXX ( PreviousDatesTable, Table[Date From] )
VAR PreviousDateRecord =
    FILTER ( PreviousDatesTable, Table[Date From] = PreviousDate )
RETURN
    MAXX ( PreviousDateRecord, Table[Status] )
    Previous Date =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
RETURN
    MAXX ( PreviousDatesTable, Table[Date From]

     

     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

dostdahl 

Apologies for this typo mistake. Please use

Previous Status =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
VAR PreviousDate =
    MAXX ( MAX ( PreviousDatesTable ), Table[Date From] )
RETURN
    CALCULATE ( Table[Status], Table[Date From] = PreviousDate )
Previous Date =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
RETURN
    MAXX ( PreviousDatesTable, Table[Date From] )

 

dostdahl's avatar
dostdahl
Icon for Helper I rankHelper I
4 years ago

Hi tamerj1 , 

 

Thank you for the update, but I'm still getting an error on the Previous Status column that states "the MAX function only accepts a column reference as an argument."

 

 And I'm still not able to use Table[Status] in the Return-Calculate statement in the end. 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    dostdahl 

    Here is the final updated solution

     

    Previous Status =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
VAR PreviousDate =
    MAXX ( PreviousDatesTable, Table[Date From] )
VAR PreviousDateRecord =
    FILTER ( PreviousDatesTable, Table[Date From] = PreviousDate )
RETURN
    MAXX ( PreviousDateRecord, Table[Status] )
    Previous Date =
VAR CurrentIdTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ID] ) )
VAR PreviousDatesTable =
    FILTER ( CurrentIdTable, Table[Date From] < EARLIER ( Table[Date From] ) )
RETURN
    MAXX ( PreviousDatesTable, Table[Date From]

     

     

     

    • dostdahl's avatar
      dostdahl
      Icon for Helper I rankHelper I
      4 years ago

      Thanks again tamerj1 -  The error message went away, but the column returns blank entries. 

       

      I'll keep looking into this, as I think it's close to what I need, but do you have any suggestions on how to get the current status/date and previous status/date to show on one line in a table?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        It shouod be possible. Can you please explain what exactly is required?