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 no. 1. In PowerBi it shows as the values True or false instead of 0 and 1.

The tablename is NSP_Tickets and column name is u_misroutereport

 

Thanks!!

Need Help

5 Replies

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    Try this one, please. 

     

    Counter =
CALCULATE (
    COUNT ( NSP_Tickets[u_misroutereport] ),
    NSP_Tickets[u_misroutereport] = TRUE ()
)

     

     

    Best Regards,

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous ,

       

      Could you please mark the proper answers as solutions?

       

       

      Best Regards,