Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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]
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!
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.
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 |
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.
Thank you so much @v-rzhou-msft, this measure works well! I appreicate the help.
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?
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.
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.
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
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 |