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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors