Formula to group similar strings under one "group" string

[Anonymous]

Hello,

I apologize if I worded my question wrong.

I'll try to explain. In an excel workbook database that I'm using to power my report in Power BI, I have several different Roles that are associated with a unique User ID that is assigned to each employee.

Some examples of different roles we have in the database are subgroups of a larger role. For example, the Project Engineer role contains sub-roles like Project Engineer - Sr, Project Engineer - Jr, Project Engineer - Intern, etc.

All of the Roles default in sub-role form when reports are generated from our data warehouse, and to simplify the data in the Dashboard, I'd like all of the sub-roles to appear as their larger parent roles in a calculated column.

For example, the formula would test the existing column to see if it contains the string Project Engineer, and would then return "Project Engineer" in the calculated column if it does. I'd have to do these for several other parent roles as well.

Can anyone help me with a dax formula that can accomplish this? I'd really appreciate it.

Thanks,

Josh

[zozozo · Accepted solution]

I dont think you need to make a custom column for this kind of grouping anymore..

Power BI already have the grouping feature released in the October, you can read it in this link October feature summary

 ...

in case you rather using dax custom column for grouping than using the power bi gui feature.

you can use nested IF, ISERROR, and SEARCH function :

for single group, ex:

Group = IF(ISERROR(SEARCH("project engineer", Yourtable[YourRoleColumn])),"Other","Project Engineer")

from dax above, every value that had text "project engineer" inside of it will become "Project Engineer" group and the rest become "Other" group

for 2 or more group use nested IF,

ex:

Group = IF(ISERROR(SEARCH("project engineer", Yourtable[YourRoleColumn])),IF(ISERROR(SEARCH("developer", Yourtable[YourRoleColumn])),"Other","Developer"),"Project Engineer")

note:

- search function is case-insensitive.

- iserror(search()) will return TRUE value if it didn't find the word so IF statement will read the TRUE and return the <value_if_true>, so you need to put the wrong value in the <value_if_true> and the right value in the <value_if_false>...

with this we can put the nested IF inside the <value_if_true>  from another if , so it become like this:

IF(<logical_test>,IF(<logical_test>,<value_if_true>,<value_if_false>),<value_if_false>)

 reference:

IF Function

Search Function

ISERROR Function

Thanks~

 edit: my potato english

My first time answering a question ... yay

[Anonymous]

Zozo,

Thanks so much for the detailed reply. I ended up doing a variation of the IF function to address this initially. I just wish I knew about the grouping function in Power BIs GUI before taking the time to write the rather lengthy formula that it took to address all the sub-roles. I'd much rather address things like this at the report level if possible.

Thanks again,

Josh