Creating a group with all values (Even if not in the data) in Power BI
Recently I was working on a client’s requirement where they wanted to group the data into groups!
Typically, when grouping data together it will have multiple ranges to group the data into.
NOTE: As far as I am aware what I am going to explain and do below is not currently supported.
PLEASE MAKE SURE YOU HAVE A BACKUP OF THE PBIX BEFORE MAKING ANY CHANGES.
ALSO PLEASE DO SO AT YOUR OWN RISK!
In my example the requirement was for the following grouping the order counts
- 1
- 2-3
- 4-6
- 7
- Others
And this is what my data looked like below.
What I then did was to create the groups as specified above.
I right clicked on the Order Count and selected “New Group”
I then changed the Group type from Bin to List
I could then see all my distinct values below in the ungrouped values.
I then selected my values and created my required groups.
As shown below I now had the following Groups and members
I then clicked Ok; I could now see my “Order Count (groups) in the report view.
The challenge I had was what happens if I get data for an order count of 5?
When I added in the data for an order count of 5, it would then default to the Group of Other as shown below.
This is now what I needed or expected. I thought it would be able to be part of group 4 & 6!
As I have shown this should be in the group of 4 & 6. If I go to edit the group, I don’t get the option to add it to my required group.
Thankfully this is where Tabular Editor comes to the rescue!
I opened Tabular Editor and went to the group’s column.
I could now see that this was created with a DAX SWITCH function
To get this working as expected I then updated the Groups to have the numbers I require.
I also updated the Group names as shown below.
Now when I went back into Power BI Desktop I was presented with a warning, because of the changes I have made I need to refresh now
I could then see my Groups now was renamed and it had the Order count in the correct group
Summary
In this blog post I have shown you how to update the groups to include all the values that you require (Please not again that this is not fully supported)
I found this useful because it allows for me to create a group with any required values going into the future and that it will be put in the right group.
Thanks for reading and if you have any comments or suggestions please let me know.
[…] This time I had a lot of values that I wanted to put into multiple groups. I also did not want to select the individual values (I have previously shown this in my blog post Creating a group with all values (Even if not in the data) in Power BI) […]