Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm trying to create a calculated column that outputs the number of breaches ("Failures") based on a set of criteria within a service level agreement. I'm currently getting the correct figures for each indvidual column, but ideally I want to sum all 6 columns - but only where the value is less than 0.
In this context, less than 0 constitutes an SLA failure, so I want to get a picture of how many failures there have been overall.
I've got as far as the below and am now getting a 1 if there are *any* failures and a 0 if there are none based on the "||" part of the syntax.
I can't seem to get past this point (knowledge/skills gap!) so would be grateful for any steers:
SLA Failures = Calculate( IF( SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Findings]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Urg. Rem. Action]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Receipt]) <0 || SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Response]) <0 , 1, 0 ) )
Hi @david_MAS ,
Based on the information you provide is difficult to get the correct answer.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler ).
Can you please post some data and expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @david_MAS
Try the following for your calculated column. It will give you the number of failures (assuming a failure is equivalent to SLA < 0)
SLA Failures = IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0 , 1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0, 1, 0) + ///etc. all the rest here following the same pattern
or another version, based on that TRUE() is 1 and FALSE() is 0 when treated as numbers
SLA Failures = ('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0)* 1 + ('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0) * 1 + ///etc. all the rest here following the same pattern
If this is a calculated column and not a measure and if you want to see a value of 3 if 3 of the columns are less than 0 then you could do the following:
SLA Failures = IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA] < 0 ,1,0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt] < 0 ,1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt] < 0 ,1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Findings] < 0,1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Urg. Rem. Action] < 0 ,1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Receipt] < 0,1, 0) + IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Response] < 0,1, 0) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |