cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

count two or more numbers in a column

Hi, 


This seems like something super easy, but yet I have tried so many different things and nothing seems to work correctly. 

 

I am trying to say, if a column shows 7 or 8 than count those numbers. So if there was 6 7's or 8 8's than say a total of 14. 

 

Here is what I have tried so far:

 

Column:

If(Or(scale of 1 to 10=7,8),1,0) - But this says DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

So I tried a measure instead of a column:

 

Count Scale=count(scale of 1 to 10)

 

Then 

 

= If(Or(count Scale = 7,8),1,0 - This still just shows full count of all submissions 

 

I then tried another option

 

 = CALCULATE(COUNT(scale of 1 to 10),'scale of 1 to 10 = 7 && 8))
 
Then I tried
 
Passive count = COUNTROWS(FILTER(ALL(scale of 1 to 10=7 && scale of 1 to 10 = 8))
 
None of them have worked yet. I am not sure where to go next. Any Ideas? 
 
1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous 

This is much simpler in Power Query. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExNTU2NjOztFSK1YlWMjc3Mza2tLCwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(Text.PositionOfAny(Text.From([Column1]), {"7","8"}, Occurrence.All)))
in
    #"Added Custom"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@Anonymous 

You this for if formula : 

Column = IF([Scale of 1 to 10]="7" || [Scale of 1 to 10]="8",1,0)
 
However you wanted to count if it is 7 or 8, so you should use the following.
Column = CALCULATE(COUNT([Scale of 1 to 10]),FILTER('Table',[Scale of 1 to 10]="7"||[Scale of 1 to 10]="8"))


For the other condition can you show a sample of the [scale of 1 to 10] column, I see it is text type, but not sure what do you mean by 6 7s or 8 8s, what are the values look like in this column. 

 

You can just show us a table with your expected output like below, so we can understand your requirement.

scale of 1 to 10 Expected Output(measure or column).
1 ?
2 ?
3 ?
...  

 

Paul

AlB
Super User
Super User

Hi @Anonymous 

This is much simpler in Power Query. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExNTU2NjOztFSK1YlWMjc3Mza2tLCwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(Text.PositionOfAny(Text.From([Column1]), {"7","8"}, Occurrence.All)))
in
    #"Added Custom"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Fowmy
Super User
Super User

@Anonymous 

If you could paste a sample of your data or some dummy data here, it will help me understand your question clearly

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Greg_Deckler
Super User
Super User

@Anonymous - Perhaps:

If(Or(scale of 1 to 10="7","8"),1,0)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Anonymous
Not applicable

Thank you Greg-Deckler, that seemed to work for not having the error anymore. Now it is not counting just the ones that are 7 or 8 but counting all the rows with a number. 

 

NPS = IF(OR('scale of 1 to 10']="7","8"),1,0)
 
I just have two test rows and it should only be showing a 0 and not a 2 since there is only a 6 and a 9. 
 
Not sure why its doing this.
 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors