Dynamic Grouping of Category

[Anonymous]

Need help with Dynamic Grouping

Consider that there are different categories –

• Extra Small
• Small
• Medium
• Large
• Extra Large

I have a checkbox where one can select the categories. –

If user selects Extra small and small, rest categories should be grouped to other.

Can someone help me to write DAX for dynamic grouping?

Thank you in advance.

[amitchandak · Accepted solution]

@Anonymous , If it based on another column, you can create a new column, using Switch 

https://www.daxpatterns.com/static-segmentation/

If you need on the measure you need to binning or segmentation

refer video: https://youtu.be/CuczXPj0N-k

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

all measures are in the sample pbix file.

Qty Total by Choice =

VAR choice =

ALLSELECTED ( Categories[Category] )

VAR currentseries =

MAX ( Data[Series] )

VAR alltable =

SUMMARIZE ( ALL ( Data ), Data[Category], Data[Series] )

VAR choicetable =

FILTER ( alltable, Data[Category] IN choice )

VAR nochoicetable =

FILTER (

ADDCOLUMNS ( EXCEPT ( alltable, choicetable ), "@qty", [Qty total] ),

Data[Series] = currentseries

)

RETURN

IF (

SELECTEDVALUE ( Data[Category]) = "_Others",

SUMX ( nochoicetable, [@qty] ),

IF ( SELECTEDVALUE ( Data[Category] ) IN choice, [Qty total], BLANK () )

)

https://www.dropbox.com/s/9iw4v4bghe75x71/weicaomis.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[amitchandak · Accepted solution]

@Anonymous , If it based on another column, you can create a new column, using Switch 

https://www.daxpatterns.com/static-segmentation/

If you need on the measure you need to binning or segmentation

refer video: https://youtu.be/CuczXPj0N-k

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization