topic Re: Boolean Column: SLA's Missed within the Current Year in DAX Commands and Tips

Boolean Column: SLA's Missed within the Current Year

TaylorClose — Fri, 07 Aug 2020 17:38:01 GMT

Hi,

I am trying to create a column for a customer account table that calculates how many tickets in a separate table have the status "SLA Missed" and were submitted within the current year, and evaluates to "True" if the number is greater than 0.

Tables:
'Excel - Ticket Data' - Contains a line per ticket
'Calendar' - Normal Calendar Table

Columns:
'Calendar'[Date] - Has normal dates within it.
'Excel - Ticket Data'[Was SLA ever missed? - Has the values "Yes" and "No" within it
'Excel - Ticket Data'[Date Submitted] - Has normal dates within it.

My first attempt at the calculation for the new column is as such:

HAS MISSED SLAS = CALCULATE(COUNTROWS('Excel - Ticket Data'), AND(FILTER('Excel - Ticket Data','Excel - Ticket Data'[Was SLA ever missed?] = "Yes"), FILTER('Excel - Ticket Data', 'Excel - Ticket Data'[Date Submitted] = DATESINPERIOD('Calendar'[Date],1/1/2020, 12, MONTH)))) > 0

The desired result is a boolean column at the account level that returns a "True" if there are tickets past SLA within the year, and "False" if not.

Any help would be appreciated!

Re: Boolean Column: SLA's Missed within the Current Year

Fowmy — Fri, 07 Aug 2020 20:36:35 GMT

TaylorClose

I modified your formula as: You can change the date "1/1/2020" to today to get the past 12 months if you need it.

HAS MISSED SLAS = VAR _COUNT = CALCULATE( COUNTROWS('Excel - Ticket Data'), 'Excel - Ticket Data'[Was SLA ever missed?] = "Yes", DATESINPERIOD('Calendar'[Date],"1/1/2020", -12, MONTH) ) RETURN IF( _COUNT > 0, TRUE(), FALSE() )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Re: Boolean Column: SLA's Missed within the Current Year

TaylorClose — Sun, 09 Aug 2020 19:26:10 GMT

Had to tweak this a bit to make Date submitted the active relationship, but this now works. Thank you!

Taylor