topic Re: How to show status updates in same row in Table in DAX Commands and Tips

How to show status updates in same row in Table

dostdahl — Tue, 05 Apr 2022 21:36:56 GMT

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

Re: How to show status updates in same row in Table

tamerj1 — Tue, 05 Apr 2022 22:35:41 GMT

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

Re: How to show status updates in same row in Table

dostdahl — Wed, 06 Apr 2022 13:11:59 GMT

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?

Re: How to show status updates in same row in Table

tamerj1 — Wed, 06 Apr 2022 15:20:11 GMT

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 )

Re: How to show status updates in same row in Table

dostdahl — Thu, 07 Apr 2022 19:37:01 GMT

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.

Re: How to show status updates in same row in Table

tamerj1 — Fri, 08 Apr 2022 22:23:57 GMT

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

Re: How to show status updates in same row in Table

dostdahl — Fri, 08 Apr 2022 00:16:03 GMT

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?

Re: How to show status updates in same row in Table

tamerj1 — Fri, 08 Apr 2022 00:58:25 GMT

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

Re: How to show status updates in same row in Table

Anonymous — Fri, 08 Apr 2022 10:10:05 GMT

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.

Re: How to show status updates in same row in Table

dostdahl — Fri, 08 Apr 2022 14:00:12 GMT

Hi Anonymous -

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

Re: How to show status updates in same row in Table

dostdahl — Fri, 08 Apr 2022 14:04:26 GMT

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

Re: How to show status updates in same row in Table

tamerj1 — Fri, 08 Apr 2022 15:59:12 GMT

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

Re: How to show status updates in same row in Table

dostdahl — Fri, 08 Apr 2022 21:52:17 GMT

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.

Re: How to show status updates in same row in Table

tamerj1 — Fri, 08 Apr 2022 22:16:11 GMT

dostdahl

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

Re: How to show status updates in same row in Table

Anonymous — Mon, 11 Apr 2022 02:06:03 GMT

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.

Re: How to show status updates in same row in Table

tamerj1 — Mon, 11 Apr 2022 02:31:10 GMT

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

Re: How to show status updates in same row in Table

dostdahl — Mon, 11 Apr 2022 12:55:00 GMT

Thank you so much Anonymous, this measure works well! I appreicate the help.

Re: How to show status updates in same row in Table

dostdahl — Mon, 11 Apr 2022 12:57:02 GMT

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