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

[Anonymous]

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?

[Anonymous · Accepted solution]

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

D

[Anonymous]

@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.

[Anonymous]

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!~

[Greg_Deckler]

Try:

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

[Anonymous]

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.

[Anonymous]

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

The following is shown

[az38]

@Anonymous
Try COUNTROWS(), not count

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

[Anonymous]

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 

[az38]

@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

[Anonymous]

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...

[Greg_Deckler]

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.

[Anonymous]

 

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.

[az38]

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]))