topic How to group/combine row definition into a specific category in DAX Commands and Tips

How to group/combine row definition into a specific category

Anonymous — Thu, 04 Mar 2021 22:58:49 GMT

Hello Experts,

I need to group/combine row variables into a specific category type. For example;

I have a column called industry category where I have row variables captured as "Supermarket/Supermarkets/SuperMkts" for three different businesses. But they are part of the same industry type. I would like to group them as "Supermarkets" so that I can visualise my data in the correct format. Now I can group them by right-clicking on the column and select "New Group". It's a cumbersome task when I need to do a similar exercise for 20k line items. I was wondering if there is a way to automate this process by writing a dax code or in power query!

I am unable to find a solution. It would be great if you could help me with this.

Thanks in advance

Regards

ronie

Re: How to group/combine row definition into a specific category

gvrajesh — Fri, 05 Mar 2021 02:49:40 GMT

Hi Anonymous

You can create a calcualted column like below

Re: How to group/combine row definition into a specific category

Anonymous — Fri, 05 Mar 2021 06:09:21 GMT

Hello gvrajesh,

Thank you for your reply. I can certainly apply your logic to search a specific keyword. Unfortunately, I have plenty to look for. Can I add multiple search rules in single containsstring function? I was thinking of this way;

CONTAINSSTRING('table name1'[column1],"Accomodation") || CONTAINSSTRING('table name1'[column1],"Apartments"), "Accomodation")

I need to add few more keywords. Is there a better way to implement it?

Thanks

Re: How to group/combine row definition into a specific category

gvrajesh — Fri, 05 Mar 2021 11:01:52 GMT

Hi Anonymous

Bestway is create lookup table like below and create calculated column

In future if you want to add new values, you can simply update this lookup table.

Create calculate column