Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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) )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |