count two or more numbers in a column

[Anonymous]

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? 

[V-pazhen-msft]

@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 · Accepted solution]

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 

[Fowmy]

@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

[Greg_Deckler]

@Anonymous - Perhaps:

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

[Anonymous]

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.