Create an aging KPI where the target/status changes based on a category in SSAS Tabular

Question

I am trying to create a single KPI for support ticket age, but the status needs to take into consideration the "Status Category". I am struggling to figure out how I would set a single KPI to have different status indicators. Below is an explanation of what I'm trying to accomplish:

Status           KPI                                                      0-.30 Days    31-60 Days  61-90 Days  > 90 Days
Vendor Ticket   Green  if all <=90, Red if any > 90                       0             0           0           12
Standard Ticket Green  if all <=30, Yellow if any 31-60, Red if Any > 60                5           0           0
Projects        Green  if all <=60, Yellow if any 61-90, Red if Any > 90  0.3           4           0           0
Waiting on Info Green  if all <=30, Yellow if any 31-60, Red if Any > 60  14            0           0           0

So in this list of ticket ages, Vendor Tickets would be red because there are 12 tickets over 90 days, Standard Tickets would be Yellow since there are 5 in the 31-60 Day bucket, but Projects would be Green even though there are 4 tickets in the 31-60 Day bucket and Waiting on Info would be Green.

I can't write up a full answer, but look at SWITCH with SELECTEDVALUE(Table[Status]). — TheRizza
– TheRizza, Commented Feb 13, 2024 at 18:07

user9517769 · Accepted Answer · 2024-02-18 19:20:57Z

As @TheRizza mentioned, you can use SWITCH(), but before you may need to add a calculated column to determine the age of each ticket in days, then :

VendorTicketKPI = 
SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] > 90)) > 0, "Red",
    "Green"
)

StandardTicketKPI = 
SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] > 60)) > 0, "Red",
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] BETWEEN 31 AND 60)) > 0, "Yellow",
    "Green"
)

ProjectsKPI = 
SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] > 90)) > 0, "Red",
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] BETWEEN 61 AND 90)) > 0, "Yellow",
    "Green"
)

WaitingOnInfoKPI = 
SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] > 60)) > 0, "Red",
    COUNTROWS(FILTER('Tickets', 'Tickets'[TicketAge] BETWEEN 31 AND 60)) > 0, "Yellow",
    "Green"
)

Collectives™ on Stack Overflow

Create an aging KPI where the target/status changes based on a category in SSAS Tabular

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related