Categorizing a column value into 3 different categories

[monishamathew]

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.

[ouaelaam · Accepted solution]

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.

[Anonymous · Accepted solution]

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 🙂

[monishamathew]

Thanks for the quick response. This works for me.

[ouaelaam · Accepted solution]

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.

[monishamathew]

Thanks. This also works for me.