Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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? 
 
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

5 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.
       
  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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