Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@Anonymous
You this for if formula :
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
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
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 🙂
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous - Perhaps:
If(Or(scale of 1 to 10="7","8"),1,0)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |