Problem with COUNT function

[a68tbird]

Hello All,

   Having a problem trying to figure this out, and it sure seems like it should be easy...

I'd like to calculate the % of discount codes redeemed. My table has CodeID (created each time a code is issued), and a BurnedOnUtc date field when a code is used.  I am able to put these fields into a simple matrix table, and the count of each of these fields is displayed (along with the relevant property that the codes relate to).  However, I can't figure out how to calculate the percentage of codes burned.  I've tried creating the following new meaures, with no luck.

Redeemed % = DIVIDE(COUNT(Codes[BurnedOnUtc], Codes[CodeID]))  

I tried creating individual measures for the counts of each:

CountBurned = COUNT(Codes[BurnedOnUtc]) and CountIssued = COUNT(Codes[CodeID]) and then using the DIVIDE statement as above.

All of these wind up with the error message: 

Error Message:

MdxScript(Model) (1, 43) Calculation error in measure 'Codes'[CountIssued]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.

I'm at a loss here. I can't see why in the matrix table, the COUNT functionality works, but I can't recreate those numbers in order to use in another calculation.  I know this is going to be one of those "slap on the forehead" moments when I see the solution...

Thanks for the help.

Travis

[GilesWalker]

@a68tbird this sounds like an easy fix. The error message is telling you that one of the fields within the equation is formatted as text, however to do divides you need it to be a number.

On the right hand side of Power BI desktop you will see your tables and columns, click on CodeID. You will notice the ribbon at the top of the page will change and show the below image:

In the formatting section you can change the data type. Currently it probably says TEXT, change this to whole number, or decimal number (depends on your needs).

This will change the formatting allowing you to use the DIVIDE function.

If this doesnt work please let me know.

Thanks,

Giles

[HarrisMalik · Accepted solution]

@GilesWalker Your solution will work if the CodeID is a numeric value but it can be a text value and @a68tbird wants to count text values in the column. 

@a68tbird Count function only works with numeric and date values in the column. See here

In case you want to count text values you need to use COUNTA() instead of COUNT()

[GilesWalker]

@HarrisMalik spot on, forgot to mention the COUNTA function, good call.

[a68tbird]

Thanks very much! COUNTA was what I needed. Simple fix.