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]

     

     

     

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

Hi Anonymous - 

Thank you for your help, these calculated columns appear to be working well. When I lay it out in a Table visual (like your attached result screenshot), the IDs that have multiple status updates appear more than once.

 

Do you know how to show each ID once, whether they have a previous status or a singular status?

 

Expected end result would look like

IDNameStatusDate FromPrevious StatusPrevious Date
1John DoeCultivation3/16/2022Solicitation3/14/2022
2Jane DoeStewardship2/23/2022  
3Mike SmithSolicitation2/24/2022Stewardship2/17/2022
Anonymous's avatar
Anonymous
Not applicable
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.

 

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

    Thank you so much Anonymous, this measure works well! I appreicate the help.