Forum Discussion
Categorizing a column value into 3 different categories
Hi folks,
I have a requirement in which I need to categorize values in App column in three categories based on the values of DeviceCompliant column.
If a particular App has both True and False , then the new column should show "Both",otherwise it would be either "true" or "False" value.
I have made a table with dummy data-
| App | DeviceCompliant |
| A | TRUE |
| B | FALSE |
| A | FALSE |
| C | TRUE |
| B | TRUE |
| D | FALSE |
| E | TRUE |
And Below is the desired result-
| App | New Column |
| A | Both |
| B | Both |
| C | TRUE |
| D | FALSE |
| E | TRUE |
Please let me know if we could achieve it at the query editor or both report and query editor.
Hello,
Here is a suggestion :
You can do a Group By with Concatenation as exlained here :
https://gorilla.bi/power-query/group-by-to-concatenate-text/Then create a conditional column with a Text.Contains to get the final result.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi monishamathew ,
I am sending the measure sample I created with the screenshot.
MEASURE = IF(calculate(DISTINCTCOUNT('Table'[New Column]),GROUPBY('Table','Table'[App]))>=2,"Both",SELECTEDVALUE('Table'[New Column]))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards 🙂
4 Replies
Hi monishamathew ,
I am sending the measure sample I created with the screenshot.
MEASURE = IF(calculate(DISTINCTCOUNT('Table'[New Column]),GROUPBY('Table','Table'[App]))>=2,"Both",SELECTEDVALUE('Table'[New Column]))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards 🙂Thanks for the quick response. This works for me.
Hello,
Here is a suggestion :
You can do a Group By with Concatenation as exlained here :
https://gorilla.bi/power-query/group-by-to-concatenate-text/Then create a conditional column with a Text.Contains to get the final result.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks. This also works for me.
