Forum Discussion
How to aggregate counts across multiple columns and rows
Hi all,
I have data that indicates how many warnings a user recieves when they perform a certain action. The data includes the last 30 instances each user performed the action and whether they recieved no warnings, one warning, or more than one warning from the system. I'd like to count how many times over all users and all instances each quantity of warnings appears.
My goal output is 3 custom columns which incate the count of each number of warnings in each row--I'd like to be able to plot these quantities in a pie or bar chart. I'm open to alternatives which would allow for the same or similar plotting.
Sampla Data:
| Unique ID | Instance 1 | Instance 2 | Instance 3 | Instance 4 | Instance 5 | Example "No Warnings" Column | Example "One Warning" Column | Example "Multi Warnings" Column |
1 | No warnings | One warning | Multiple warnings | No warnings | Multiple warnings | 2 | 1 | 2 |
| 2 | One warning | One warning | No warnings | Multiple warnings | One warning | 1 | 3 | 1 |
Try something like this. You would need one for each column.
No Warning = VAR _String = "No warnings" VAR _1 = 'Table'[Instance 1] = _String VAR _2 = 'Table'[Instance 2] = _String VAR _3 = 'Table'[Instance 3] = _String VAR _4 = 'Table'[Instance 4] = _String VAR _5 = 'Table'[Instance 5] = _String RETURN _1 + _2 + _3 + _4 + _5Just change what it is looking for in _String
4 Replies
- jdbuchanan71
Super User
Try something like this. You would need one for each column.
No Warning = VAR _String = "No warnings" VAR _1 = 'Table'[Instance 1] = _String VAR _2 = 'Table'[Instance 2] = _String VAR _3 = 'Table'[Instance 3] = _String VAR _4 = 'Table'[Instance 4] = _String VAR _5 = 'Table'[Instance 5] = _String RETURN _1 + _2 + _3 + _4 + _5Just change what it is looking for in _String
Makes sense.
In Power Query I'm seeing a "Token EoF expected" popping up at the first line:
= VAR _String = "No warnings"
Additionally, "RETURN" is being corrected to "Type.FunctionReturn"
- jdbuchanan71
This would not be done in powerquery. It is just a calculated column done in DAX
