Group by selected variables in column

DAX DAX Calculations
1

I have 100 product names in a column.

I want to group them by their names to make category analysis easier.

Example
Checking
Free Checking
HSA Checking
Freedom Checking
Auto loan
New auto loan
Used auto loan

I want to group the above and have two products: Checking and Auto Loan.

What is the best way to facilitate this?

THANK YOU!

2

@rizzotony ,

Four for the price of one today:

DAX Measure:

Account =
SWITCH(TRUE),
CONTAINSSTRING( SELECTEDVALUE(Table[Column), “checking”), “Checking”,
CONTAINSSTRING( SELECTEDVALUE(Table[Column), “auto loan”), “Auto Loan”,
BLANK()
)

DAX Calculated Column:
same as above, but without SELECTEDVALUE

M code in Power Query
Account =
if Text.Contains( [Column], “checking”, Comparer.OrdinalIgnoreCase) then “Checking” else
if Text.Contains( [Column], “auto loan”, Comparer.OrdinalIgnoreCase) then “Auto Loan” else
null

Also, you can create dynamic groups through the Power BI User Interface. Sam put out a video on this last week

I hope this is helpful.

  • Brian
3 Likes
3

Thanks Brian! You are amazing. Looking forward to executing your advice.

Tony

1 Like
4

@rizzotony ,

Thanks - glad to help. Even if you end up using one of the first three approaches, I definitely recommend checking out @sam.mckay’s video – it’s one of those really cool features tucked away in Power BI that many people never even notice, but good to have in your back pocket as a quick and flexible way of handling a lot of different grouping related tasks.

  • Brian
1 Like
5

Hi @rizzotony, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi @rizzotony, we’ve noticed that no response has been received from you since July 2nd. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @rizzotony, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.