cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## DAX Formula to group categories in One column

I have an issue and trying to figure it out.  I have two columns that need to somehow be assessed and it isnt working for me.

Column A is an ID column that has 1000's of unique IDS.

Column B has specific categories

Business Rules: When a category is selected, the end-user wants to know what the % of that category is alongside the % of all other categories that shared that ID.

The end-user wants to do some predicitive analysis and asked that we show them what % of each category.  So in the below scenario, if someone selected DEF in a slicer,  a pie chart would show DEF as 50% (2) and EFG as 50% (2).  Basically if DEF is selected, what is the percentage of all other categories in that column sharing the same ID.

Sample Data

ID           CATEGORY

1             ABC

2             DEF

2             EFG

3             DEF

3             EFG

I cant use ID as a slicer because there are 1000's and it just doesnt work.   Was wondering if someone can get me in the right direction for some type of DAX formula or alteration of the data set to get this functioning.

2 ACCEPTED SOLUTIONS
Super User

Hi @common763,

Okay, I've managed to build the pie chart you need with my toy data. Please try this approach with your production dataset.

The idea is:

1) You create a new table with the unique values from the Category column.

``Cats = DISTINCT ( data[Category] )``

It should be standalone, not linked with the main table.

2) You create a measure like this:

``````Filtered Count =
VAR SelectedValues = VALUES ( data[ID] )
VAR ActiveCategory = MIN ( Cats[Category] )
RETURN COUNTX ( FILTER ( ALL ( data ), AND ( data[ID] IN SelectedValues, data[Category] = ActiveCategory ) ), [Category] ) ``````

3) You add a Category slicer on your dashboard based on the column of your main table.

4) You add a Pie Chart visual and use Cats[Category] and the measure from p. 2.

Voila:

Best Regards,

Alexander

Helper III

Excellent.  I will try this out.  Thanks so much.  Get back when I work it out on my end.

5 REPLIES 5
Helper III

Excellent Job.  Works perfectly.  Thanks so much.

Super User

Hi @common763,

If I have correctly understood your task, you need a measure like this:

In text format for convenience:

``````Measure =
VAR Num = COUNT ( data[ID] )
VAR IDSet = VALUES ( data[ID] )
VAR Denom = COUNTX ( FILTER ( ALL ( data ), data[ID] IN IDSet ), [Category] )
RETURN DIVIDE ( Num, Denom )``````

Best Regards,

Alexander

Helper III

Ok sorry for the late response.  Our team had a solution but it seems like a band aid.  This kind of gets me there but doesnt properly group based off the selection.  The customer wants to single select a category, so say the select ABC in a slicer.  They then want a pie graph to display the total for ABC and all other categories with the same ID.    So with the below:

1-ABC

1-DDD

1-CCC

1-EEE

2-ABC

2-CCC

2-EEE

Output would equal ABC = 2  DDD = 1 CCC = 2 EEE = 2 or by % of those numbers

We ended up creating two replicated tables, then many to many relationships and it works in BI Desktop, but when I tried to redo it in Report Server I am getting errors and thinking there has to be a better way to handle this.  Any information would be greatly appreciated.  Thanks.

Super User

Hi @common763,

Okay, I've managed to build the pie chart you need with my toy data. Please try this approach with your production dataset.

The idea is:

1) You create a new table with the unique values from the Category column.

``Cats = DISTINCT ( data[Category] )``

It should be standalone, not linked with the main table.

2) You create a measure like this:

``````Filtered Count =
VAR SelectedValues = VALUES ( data[ID] )
VAR ActiveCategory = MIN ( Cats[Category] )
RETURN COUNTX ( FILTER ( ALL ( data ), AND ( data[ID] IN SelectedValues, data[Category] = ActiveCategory ) ), [Category] ) ``````

3) You add a Category slicer on your dashboard based on the column of your main table.

4) You add a Pie Chart visual and use Cats[Category] and the measure from p. 2.

Voila:

Best Regards,

Alexander

Helper III

Excellent.  I will try this out.  Thanks so much.  Get back when I work it out on my end.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors