Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Count rows with specific value

I have a table in a SQL database that contains 3 diffrent "values": Null, 0 and 1                                 I need to get the nu,ber of rows back where it only counts the...
Need Help
yelsherif's avatar
yelsherif
Resolver IV
7 years ago

create a measure to make condistional count as follows
Counter = CALCULATE(COUNT(NSP_Tickets[u_misroutereport]),NSP_Tickets[u_misroutereport] = 1)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

this gives me the following error:

dxScript (Model) (4, 78) Calculation error in measure 'NSP_Tickets' [Counter]: True / False values cannot be compared with integer values in DAX comparisons. Consider using the VALUE or FORMAT function and converting one of the values.

  • yelsherif's avatar
    yelsherif
    Resolver IV
    7 years ago

    Ok, seems the values are not integers, please state the type of this column (text, boolean, ..etc)

     

    You can modify the formula to deal with the column type as follows

    Counter = CALCULATE(COUNT(NSP_Tickets[u_misroutereport]),NSP_Tickets[u_misroutereport] = "1")
    or
    Counter = CALCULATE(COUNT(NSP_Tickets[u_misroutereport]),VALUE(NSP_Tickets[u_misroutereport]) = 1)