Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
dostdahl
Helper I
Helper I

How to show status updates in same row in Table

‎04-05-2022 02:36 PM

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

Message 1 of 18
1,465 Views
0
2 ACCEPTED SOLUTIONS
v-rzhou-msft
Community Support
Community Support
In response to dostdahl

‎04-10-2022 07:06 PM

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

Message 15 of 18
1,057 Views
tamerj1
Super User
Super User

‎04-10-2022 07:28 PM

@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

Message 17 of 18
1,052 Views
17 REPLIES 17
tamerj1
Super User
Super User

‎04-10-2022 07:28 PM

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

 

 

 

Message 17 of 18
1,053 Views
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-11-2022 05:57 AM

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

Message 18 of 18
1,029 Views
v-rzhou-msft
Community Support
Community Support

‎04-08-2022 03:10 AM

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.

 

 

Message 13 of 18
1,265 Views
0
dostdahl
Helper I
Helper I
In response to v-rzhou-msft

‎04-08-2022 07:00 AM

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
Message 14 of 18
1,256 Views
0
v-rzhou-msft
Community Support
Community Support
In response to dostdahl

‎04-10-2022 07:06 PM

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.

 

Message 15 of 18
1,058 Views
dostdahl
Helper I
Helper I
In response to v-rzhou-msft

‎04-11-2022 05:55 AM

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

Message 16 of 18
1,030 Views
0
tamerj1
Super User
Super User

‎04-05-2022 03:35 PM

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] )
Message 2 of 18
1,416 Views
0
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-06-2022 06:11 AM

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?

Message 3 of 18
1,371 Views
0
tamerj1
Super User
Super User
In response to dostdahl

‎04-06-2022 08:20 AM

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

 

Message 4 of 18
1,367 Views
0
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-07-2022 12:37 PM

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. 

Message 5 of 18
1,334 Views
0
tamerj1
Super User
Super User
In response to dostdahl

‎04-07-2022 12:44 PM

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

 

 

 

Message 6 of 18
1,331 Views
0
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-07-2022 05:16 PM

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?

Message 7 of 18
1,294 Views
0
tamerj1
Super User
Super User
In response to dostdahl

‎04-08-2022 08:59 AM

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

 

Message 10 of 18
1,230 Views
0
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-08-2022 02:52 PM

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. 

Message 11 of 18
1,211 Views
0
tamerj1
Super User
Super User
In response to dostdahl

‎04-08-2022 03:16 PM

@dostdahl 

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

Message 12 of 18
1,206 Views
0
tamerj1
Super User
Super User
In response to dostdahl

‎04-07-2022 05:58 PM

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

Message 8 of 18
1,281 Views
0
dostdahl
Helper I
Helper I
In response to tamerj1

‎04-08-2022 07:04 AM

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
Message 9 of 18
1,254 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All