cancel
Showing results 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

Helper III

Status of most recent task

I have a table of appointments that fall into 2 categories- Confirmed or Unconfirmed.  For each Unconfirmed, there is a reason.  I need to return the reason based on only the most recent unconfirmed appointment.  I can calculate when the most recent unconfirmed appointment was, but I can't figure out how to return the reason.

Data

 Appointment Date Status Reason 5/1/24 Confirmed 6/1/24 Unconfirmed Cancelled 7/1/24 Unconfirmed No Show

My formula for most recent unconfirmed date would be:
Most Recent = CALCULATE(MAX('Table'[Appointment Date]), 'Table'[Status]="Unconfirmed", 'Table'[Appointment Date]<=TODAY()

Using the data sample above, I would get 7/1/24 as my result.  Now, how do I get "No Show"?

Thank you in advance for any help

1 ACCEPTED SOLUTION
Solution Specialist

hello @aflintdepm

your DAX looks good. If you want to get 'Reason' value, just use your DAX but change value inside MAX from date into reason.

`Most Recent Reason = CALCULATE(MAX('Table'[Reason]), 'Table'[Status]="Unconfirmed", 'Table'[Appointment Date]<=TODAY())`

Thank you.

3 REPLIES 3
Super User

or try to plot a table visual with date column, status column and a measure like:

``````Measure =
VAR _currentdate = MAX(data[date])
VAR _statuspre =
MAXX(
TOPN(
1,
FILTER(ALL(data), data[date]<_currentdate),
data[date]
),
data[status]
)
VAR _result =
SWITCH(
_statuspre,
"Confirmed", "Cancelled",
"Unconfirmed", "No Show",
""
)
RETURN _result``````

it worked like:

Super User

Hi @aflintdepm ，

not sure if i fully get you, try to add a calculated column like:

``````column =
VAR _currentdate = [date]
VAR _statuspre =
MAXX(
TOPN(
1,
FILTER(data, data[date]<_currentdate),
data[date]
),
data[status]
)
VAR _result =
SWITCH(
_statuspre,
"Confirmed", "Cancelled",
"Unconfirmed", "No Show",
BLANK()
)
RETURN _result``````

it works like:

Solution Specialist

hello @aflintdepm

your DAX looks good. If you want to get 'Reason' value, just use your DAX but change value inside MAX from date into reason.

`Most Recent Reason = CALCULATE(MAX('Table'[Reason]), 'Table'[Status]="Unconfirmed", 'Table'[Appointment Date]<=TODAY())`

Thank you.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.