Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
swallia23
Frequent Visitor

How to count number of rows when a column equals a text value in Power BI

My situation:  I need to count the rows of 'Open' tickets. Through YouTube and this Forum, I came up with a couple of measures that I thought would work for sure.  However, I keep getting the error that is posted below.  I'm very new to Power BI and I consider myself tech savy, but I'm not a coder.  I definitely appreciate any assistance or directions.

 

EOM_tickets_open = COUNTX(FILTER(new_Tickets,[new_Status ID]="Open"),new_Tickets[new_Status ID])
or 
COUNTIF_Open = CALCULATE(COUNTROWS(new_Tickets),new_Tickets[new_Status ID] = "Open")
 

swallia23_1-1715978368335.png

 

Much thanks, Joe

1 ACCEPTED SOLUTION

 

Assuming it is integer and you have the measure in the new_Tickets tables, 

What is the output you are getting?

 

COUNTIF_Open = CALCULATE(COUNTROWS(new_Tickets), 
             Filter( ALLSELECTED(new_Tickets), new_Tickets[new_Status ID] = 6) )

 

 

View solution in original post

7 REPLIES 7
swallia23
Frequent Visitor

Thanks @sevenhills , I am still getting the same errors. However, I'm making note of your syntax since I am a noob.  @Anand24  See below.  the 'new_Status ID' column is an integer, but it is an index number to the 'new_Status ID' table.  For giggles, I replaced "Open" with "6" and I still get the same error.

swallia23_0-1715989028815.png

swallia23_1-1715989052559.png

I can't tell you how much I appreciate everyone's help

Thanks again, Joe

sevenhills
Super User
Super User

First, let us fix the error!
The error is saying that you are comparing integer/number values to text. Check the column used in the filter, whether it is integer/number or text? As per the error, it is integer. Also, do check the values in this column ... 

 

sevenhills_0-1715979543246.png

 

 

Coming to Dax portion, try this!

COUNTIF_Open = CALCULATE(COUNTROWS(new_Tickets), 
                                    Filter( new_Tickets, new_Tickets[new_Status ID] = "Open")
                              )

rarely it may need like this:

COUNTIF_Open = CALCULATE(COUNTROWS(new_Tickets), 
                                    Filter( ALLSELECTED(new_Tickets), new_Tickets[new_Status ID] = "Open")
                              )

 

Assuming it is integer and you have the measure in the new_Tickets tables, 

What is the output you are getting?

 

COUNTIF_Open = CALCULATE(COUNTROWS(new_Tickets), 
             Filter( ALLSELECTED(new_Tickets), new_Tickets[new_Status ID] = 6) )

 

 

@sevenhills my apologies, initially, I put the 6 within quotes. I removed the quotes and now I get a result, though not the expected result.

swallia23_1-1715992797704.png

swallia23_2-1715992824918.png

 

doing some more checking

Is the measure, columns used in the measure and visual are in the same table? 

 

If not check the data model and see the tables are linked.

@sevenhills and @Anand24 , thanks for you help understanding this error.  It was exactly what the error was stating, I was trying to compare an interger and text.  I thought that since the 'new_Status ID' column was an index to the new_Status ID table, that Power BI would make the connection.  But, when I simply use the index number, the error was removed.  So, I will keep playing with DAX to see if I can get the end result that I need, and I will most likely start another thread requesting help with that since it seems it will be a tough ask for my knowledge.  Thanks again for the assistance, much appreciated.

Anand24
Super User
Super User

Hi @swallia23 ,
The error says the issue is in the comparison (filtering condition). It suggests that new_Tickets[new_Status ID] is an integer field and not text field. Can you show what values you have in new_Tickets[new_Status ID] ?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.