Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dostdahl
Helper I
Helper I

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

 

IDNameStatusDate FromPrevious StatusPrevious Date
1John DoeCultivation3/16/2022Solicitation3/14/2022
1John DoeSolicitation3/14/2022  
2Jane DoeStewardship2/23/2022  
3Mike SmithSolicitation2/24/2022Stewardship2/17/2022
3Mike SmithStewardship2/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

2 ACCEPTED SOLUTIONS

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.

RicoZhou_0-1649642744252.png

 

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.

 

View solution in original post

tamerj1
Super User
Super User

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

 

 

 

View solution in original post

17 REPLIES 17
tamerj1
Super User
Super User

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

 

 

 

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
Community Support
Community Support

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.

RicoZhou_0-1649412593053.png

 

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.

 

 

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

IDNameStatusDate FromPrevious StatusPrevious Date
1John DoeCultivation3/16/2022Solicitation3/14/2022
2Jane DoeStewardship2/23/2022  
3Mike SmithSolicitation2/24/2022Stewardship2/17/2022

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.

RicoZhou_0-1649642744252.png

 

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.

 

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

tamerj1
Super User
Super User

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

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?

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

 

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. 

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

 

 

 

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?

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

 

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. 

@dostdahl 

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

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

My current table looks like this

IDNameStatusDate FromPrevious StatusPrevious Date
1John DoeCultivation3/16/2022Solicitation3/14/2022
1John DoeSolicitation3/14/2022  
2Jane DoeStewardship2/23/2022  
3Mike SmithSolicitation2/24/2022Stewardship2/17/2022
3Mike SmithStewardship2/17/2022  

 

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.