Grouping in Power BI Desktop Without Using DAX

Way back in Oct-2016, Microsoft released a feature in Power BI Desktop that makes the process of creating groups much easier for the average user. I was reminded of this feature by an article by Reid Havens a couple of months ago. I blogged about various other ways of doing this, including banded columns using DAX here, and then about Conditional Columns in Power Query (Power BI Desktop version) here. But the first of these required writing DAX to arrive at the solution and the second one required knowledge of Power Query. You can learn both of these with proper guidance and once you master the skills you can solve any business problem. This blog post shows you how to use the grouping feature in Power BI Desktop for text or numbers in the Report view itself.

Grouping Values in a Text Column

Consider the Color column in the Products table in AdventureWorks database.  Products can have different colours and there may be a requirement to group the colours into Light Colours and Dark Colours, for example. Of course you can do this with a calculated column using DAX or adding a conditional column in Power Query as I previously explained.  But with the  Power BI Desktop feature “New group“, you can do this easily in the Report view itself using the UI.

  • Right-click on the color column (#1 below) in the Fields list and select New group (#2 below).

This opens the Groups dialog box as shown below, and lists all the values in the color column.

  • Select all the dark colours using Ctrl Click, or Shift Click (#1 below) and then click the Group button (#2 below).

The selected colours get added under Groups and members (#1 below).

  • Name the group as Dark Colours (#2 below).

  • Select all the light colours (#1 below), click on Group and then name the Group as Light Colours (#2 below).

  • Check the box Include Other group (#1 below).

A group by name Other (#2 below) gets created that will contain all the previously un-grouped values.  This step is optional. Should the list of values grow in the future, the new items will be added to “Other”.

Now you can add the newly created group (#1 below) to your Matrix (#2 below) and the colours grouped as above will be displayed (#3 below).

Editing Groups

It is not immediately obvious, but after you create a group, there doesn’t seem to be any way to edit it.  But there IS a trick.  First click on the group name on the right, then hold Shift and click on any item from the left (that you forgot to add to the group), then click the group button.

Grouping Values in a Numeric Column

Suppose you are interested to know the Total Sales by Customer age. In the Customers table, you have BirthDate column. You can add a calculated column Age as follows.

Age = DATEDIFF(Customers[BirthDate], TODAY(), YEAR)

Next, try to plot the data in a Clustered Column chart.

As you can notice, the chart is quite noisy and it is difficult to deduce anything from that.

One solution for this is to have age groups on the x-axis instead of age itself. And it can easily be done by using the grouping feature in the Report view itself.

  • Right-click on the Age column and select New group.

The Groups dialog box in the case of numeric column (Age) is as shown below.

You can notice that the Group type is selected as Bin (#1), Bin Type as Size of bins (#2), and Bin size as 10. You can also see that the min (#4) and max (#5) values in the column are displayed.

You can now add the Age(Bins) group thus created to the x-axis in place of Age.

Your clustered column looks as below.

This chart shows the distribution of age groups in a way that is easier to consume and understand.

4 thoughts on “Grouping in Power BI Desktop Without Using DAX”

  1. KCinMelbourne

    Hi Matt, I’ve just built a model where I used grouping quite a bit to create hierarchies etc. To Mark’s point, I chose to use group’s because it was easier for the Finance team members to maintain than using DAX.

    However, one issue I’ve found is that to edit the groups, you need to download the model, make the adjustment and re-publish. This in itself isn’t a problem however when you download to .pbix, the parameters which are edited in Power BI service aren’t included.

    For example, I use parameters to set the current month or the current version of forecast (among others). If the model was last published 2 months ago, those parameters may have been updated numerous times over those months directly in PBI Service. When the model is downloaded, it will revert to the original parameters when published and will need to be updated again when re-published to service. This is fine when you have 1 or 2 but we have quite a few so we are at risk every time someone needs to kick back to PBI Desktop that they miss updating one of the parameters. Naturally DAX wouldn’t solve this issue either but I’m now looking at pushing that data into other systems where it can be maintained with less risk as the end user won’t need to download the model.

    My other alternative was possibly putting my parameters into a spreadsheet so downloading and republishing the model wasn’t dependent on the parameters solution.

    I thought I’d call this out but also, see if perhaps you have any suggestions for work arounds.

    Cheers
    KC

    1. Interesting. I wonder if you can do your parameters another way. I normally create a calculated column to flag the “current month”. Calc columns update automatically when you refresh. If you can write a rule, you can make it work.

  2. Aside from possible ease, do you know of any advantage to doing it this way vs. in DAX or Power Query?

    (Maybe tidier model, depending on how one thinks about it. Maybe saving a tiny tiny bit of data size, depending on how that group info is stored? Anything else?)

    1. I think this approach is good if you want to quickly group something adhoc. If you have very long lists of items to group, then it is a bit cumbersome. The editing is hard to do as you can’t add/remove items from each group easily if you make a mistake. If you have more permanent, regular needs, I would suggest creating a new column in the lookup table itself.

Leave a Comment

Your email address will not be published. Required fields are marked *