topic Re: Having some issues with a DAX measure for SLA with True and False in DAX Commands and Tips

Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 19:09:39 GMT

Hello all,

I'm having a DAX error about calculating SLA based on True/False values (meaning the percentage of those that met SLA). This seems fairly simple but I receive the following using this data and the following for a measure I created called sla_percent

sla_percent = CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]), 'ServiceNow Incident Data'[target_sla] = "True") / CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]))

Thoughts anyone on why I receive this error and what I might be able to do to remedy it?

Re: Having some issues with a DAX measure for SLA with True and False

az38 — Thu, 27 Feb 2020 19:30:44 GMT

Hi Anonymous

looks pretty easy - you are trying to compare Boolean field [target_sla] with text string "True". Use TRUE(), like

sla_percent = CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]), 'ServiceNow Incident Data'[target_sla] = TRUE()) / CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]))

Re: Having some issues with a DAX measure for SLA with True and False

Greg_Deckler — Thu, 27 Feb 2020 19:32:18 GMT

Try:

sla_percent = CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]), 'ServiceNow Incident Data'[target_sla] = TRUE()) / CALCULATE(COUNT('ServiceNow Incident Data'[target_sla]))

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 20:48:56 GMT

Thank you Greg_Deckler - I was just trying a similar but with Filter - which didn't work. Let me give this one a try and I'll let you know how it works in a few.

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 20:53:09 GMT

az38 Greg_Deckler The referenced DAX did not work, it has the same error as I used with Filter.

The following is shown

Re: Having some issues with a DAX measure for SLA with True and False

az38 — Thu, 27 Feb 2020 20:57:02 GMT

Anonymous
Try COUNTROWS(), not count

sla_percent = CALCULATE(COUNTROWS('ServiceNow Incident Data'), 'ServiceNow Incident Data'[target_sla] = TRUE()) / CALCULATE(COUNTROWS ('ServiceNow Incident Data'))

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 21:09:45 GMT

Well, I tried two different things but I do at least get a number value but only the value of "1".

I tried at first to use filter and set data type to Text instead which produced a value of "1"

I did the same as recommended with count rows and I also get "1"

I would think this would be a far larger number (less than 100 of course) as I have almost 5000 records and true is present in at least half of them.

Thank you! az38

Re: Having some issues with a DAX measure for SLA with True and False

az38 — Thu, 27 Feb 2020 21:18:40 GMT

Anonymous
It looks like format issue
Pick your measure in right lane, go to the Modeling ribbon and press % and set 2 as decimal places

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 21:36:41 GMT

Hmm.. I was just speaking with a collegue on this. The logic seems right and I know there are False values in there because I can filter on them manually. I can assure you though that it's not 1% or 100% - let me post both values and the actual counts for what we know the number should be. I do agree, this should be simple...

Re: Having some issues with a DAX measure for SLA with True and False

Greg_Deckler — Thu, 27 Feb 2020 21:53:13 GMT

Right, the issue with COUNT was not the same error. You need to use COUNTA to count boolean columns instead of COUNT, or COUNTROWS works as well.

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Thu, 27 Feb 2020 22:29:50 GMT

sla_percent = CALCULATE(COUNTA('ServiceNow Incident Data'[target_sla]), 'ServiceNow Incident Data'[target_sla] = "TRUE") / CALCULATE(COUNTA('ServiceNow Incident Data'[number]))

I have the values set to Text in this case. Of 4587 rows, I have 1647 listed as False. I also did a sample test with just a tiny table of four rows and one column, even added another called ID with values 1-4 that looks like this

target_sla id

TRUE 1

TRUE 2

FALSE 3

TRUE 4

Clearly this value should be 0.75 or 75% but when I just drag them to a new table visualization, they show distinct values of only true/false. I have a feeling this is the root of the cause as my actual data is very similar. This is most baffling for what appears to be simple and correct logic.

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Sat, 29 Feb 2020 12:28:32 GMT

sla_percent = DIVIDE ( CALCULATE ( COUNTROWS ( 'ServiceNow Incident Data' ), // This column must be a BOOLEAN column. // Otherwise it'll not work. KEEPFILTERS ( 'ServiceNow Incident Data'[target_sla] ) ), COUNTROWS ( 'ServiceNow Incident Data' ) )

Best

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Mon, 02 Mar 2020 15:21:01 GMT

Anonymous Greg_Deckler az38

Ok, so I now I think I see your perspective and what may be missing from this discussion. It appears that when I use a blank table with a test row labed the same and the forementioned DAX faunctions, I get the expected result. I can see why people think I'm not doing something right. I also used the latest and it too returns a result correct in my test table but not in my data.

I have a feeling this is the result of the target_sla column being performed by a conditional column, which has logic based on a previous custom column, which calculates duration from the difference of two columns. It seems that all of these provide the value of one when used in that context, which may explain that perhaps only one value is shown. I'm going to give everyone a test PBIX file to view in a few minutes which will explain this vs the test tables.

Re: Having some issues with a DAX measure for SLA with True and False

Anonymous — Mon, 02 Mar 2020 16:51:17 GMT

I feel very dumb on this one. Turns out the reason it would never work is that I had a time filter being applied to just that one visual. Another intresting note is that the COUNTROWS posted earlier seems to work best. Thank you everyone for awesome assistance!~