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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sigridr
Helper I
Helper I

Count rows containing value

Hi, 

 

I'm trying to count the rows containing specific values. 

I've tried this: 

Table.FindText(PQPFocus[Vekt utenfor norm],2) 

... but I'm getting a cyclic error. 

 

My column [Vekt utenfor norm] contains the values 5, 2, norm, -2, -5. 
I need to know how many rows contain the value 5, how many contains 2 and how many that contains the sum of those. 

Any suggestions? 🙂 

1 ACCEPTED SOLUTION
Querynalyst
Frequent Visitor

Hello,

 

I would suggest you to use DAX instead of Power Query for your need.

 

Example:

NumberOfTwo = CALCULATE(
                COUNT(PQPFocus[Vekt utenfor norm]),
                    PQPFocus[Vekt utenfor norm] = 2
                        )

 

Same thing for 5 etc.
 
I hope this will answer your question.

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

For counting 2, use following (Replace Previous Step with your previous step)

 

= List.Count(List.FindText(#"Previous Step"[Vekt utenfor norm],"2"))

 

For any other value say 5, replace 2 with 5.

sigridr
Helper I
Helper I

I used countrows, but I need it to give me the number in each row, not just mark the numbers with 1...

 

Querynalyst
Frequent Visitor

Hello,

 

I would suggest you to use DAX instead of Power Query for your need.

 

Example:

NumberOfTwo = CALCULATE(
                COUNT(PQPFocus[Vekt utenfor norm]),
                    PQPFocus[Vekt utenfor norm] = 2
                        )

 

Same thing for 5 etc.
 
I hope this will answer your question.

I tried applying this calc to my issue, but it didn't work, can you tell me what i did wrong? Or maybe I'm using it wrong. I have a survey table (Form1) with column [Language]. I'm trying to create a visual table that contains a column for where the survey originated [Center], and then  column English with the total of English language surveys submitted for that center, and again with a column for Spanish. I thought i could just create a measure to count the occurange of English and add that to my table but the calc below does not work. The rest of the calc is valid, but it doesnt recognize "english". Maybe if I transform it to a numeric value? Thanks so much for your help!

 

English = CALCULATE(COUNT(Form1[Language]),Form1[Language]="English")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors