How to show status updates in same row in Table

[dostdahl]

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 each name. Per the example table below, it's showing each individual status change per ID. If an ID only has one current status/date associated with it, only one line needs to show (ex. ID #2). But if an ID has 2 or more status updates assoicated with it, I need the current status/date and previous status/date to show on the same line (ex. ID #1 and #3). 

ID	Name	Status	Date From	Previous Status	Previous Date
1	John Doe	Cultivation	3/16/2022	Solicitation	3/14/2022
1	John Doe	Solicitation	3/14/2022
2	Jane Doe	Stewardship	2/23/2022
3	Mike Smith	Solicitation	2/24/2022	Stewardship	2/17/2022
3	Mike Smith	Stewardship	2/17/2022

I've tried a few different ways to solve this with DAX but haven't had any luck. 

Thank you in advance for any assistance, please let me know if I can provide any additional information. 

-Dereck

[v-rzhou-msft · Accepted solution]

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 · Accepted solution]

@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]

Thank you so much @tamerj1 - These columns are now returning the values that I need. Really appreciate your time and effort with this!

[v-rzhou-msft]

Hi @dostdahl ,

You can try my code to create two calculated columns.

Previous Status = 
VAR _MAXDATE =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Date From]
    )
VAR _MAX_PREVIOUS_DATE =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Date From] < _MAXDATE
        ),
        'Table'[Date From]
    )
VAR _PREVIOUS_Status =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER ( 'Table', 'Table'[Date From] = _MAX_PREVIOUS_DATE )
    )
RETURN
    IF ( 'Table'[Date From] = _MAXDATE, _PREVIOUS_Status )

Previous Date = 
VAR _MAXDATE =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Date From]
    )
VAR _MAX_PREVIOUS_DATE =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Date From] < _MAXDATE
        ),
        'Table'[Date From]
    )
RETURN
    IF ( 'Table'[Date From] = _MAXDATE, _MAX_PREVIOUS_DATE )

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]

Hi @v-rzhou-msft - 

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

ID	Name	Status	Date From	Previous Status	Previous Date
1	John Doe	Cultivation	3/16/2022	Solicitation	3/14/2022
2	Jane Doe	Stewardship	2/23/2022
3	Mike Smith	Solicitation	2/24/2022	Stewardship	2/17/2022

[v-rzhou-msft · Accepted solution]

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]

Thank you so much @v-rzhou-msft, this measure works well! I appreicate the help. 

[tamerj1]

Hi @dostdahl 

new column:

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] )
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]

Thank you for the suggestion, but I'm still running into an issue.

For the Previous Status calculated column, the Return-Calculate functions gives an error when trying to use Table[Status] as the expression.

The error is "a single value for column 'Status' in Table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result".

I tried making the Status column a measure, using SELECTEDVALUE(Table[Status]), but still no luck. Any other suggestions?

[tamerj1]

@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]

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]

@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]

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]

@dostdahl 
are you creating a new column or a measure? This is a code for a calculated column not a measure. However, it could be EARLIER that makes trouble. Please try this with a variable instead

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

[dostdahl]

I had been creating as a column. The Previous Date column you created seemed to be working great, it was the Previous Status column that was returning blanks. 

[tamerj1]

@dostdahl 

I had edited the code in the previous reply which contains the code of both columns. Please try and let me know. 

[tamerj1]

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

[dostdahl]

My current table looks like this

ID	Name	Status	Date From	Previous Status	Previous Date
1	John Doe	Cultivation	3/16/2022	Solicitation	3/14/2022
1	John Doe	Solicitation	3/14/2022
2	Jane Doe	Stewardship	2/23/2022
3	Mike Smith	Solicitation	2/24/2022	Stewardship	2/17/2022
3	Mike Smith	Stewardship	2/17/2022

Expected end result would look like

ID	Name	Status	Date From	Previous Status	Previous Date
1	John Doe	Cultivation	3/16/2022	Solicitation	3/14/2022
2	Jane Doe	Stewardship	2/23/2022
3	Mike Smith	Solicitation	2/24/2022	Stewardship	2/17/2022