cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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)

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

v-jiascu-msft Super Contributor
Super Contributor

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.
v-jiascu-msft Super Contributor
Super Contributor

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,492 guests
Please welcome our newest community members: