cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Malony Frequent Visitor
Frequent Visitor

Count rows with specific value

I have a table in a SQL database that contains 3 diffrent "values": Null, 0 and 1

 

PB.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

5 REPLIES 5
yelsherif Member
Member

Re: Count rows with specific value

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

Malony Frequent Visitor
Frequent Visitor

Re: Count rows with specific value

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

Re: Count rows with specific value

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)

Community Support Team
Community Support Team

Re: Count rows with specific value

Hi @Malony ,

 

Try this one, please. 

 

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

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Count rows with specific value

Hi @Malony ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.