topic Grouping products together by count less than 5 = other in DAX Commands and Tips

Grouping products together by count less than 5 = other

Jay2022 — Wed, 25 Sep 2024 12:28:00 GMT

I'm trying to build a bar chart of products showing all and then grouping all those where teh count is less than 5 into a category called other. Any idea how i can do this ?

Re: Grouping products together by count less than 5 = other

moncx — Wed, 25 Sep 2024 12:37:54 GMT

Hey,

there is older post on this topic in this forum, have you tried the solution provided here: Solved: Create miscellaneous/other group using count below... - Microsoft Fabric Community

Hope it helps!

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Grouping products together by count less than 5 = other

Jihwan_Kim — Wed, 25 Sep 2024 19:59:38 GMT

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

count measure with other category: = VAR _allcount = CALCULATE ( [count measure:], REMOVEFILTERS ( product_dimension ) ) VAR _lessthanfiveall = SUMX ( FILTER ( ADDCOLUMNS ( ALL ( product_dimension[product], product_dimension[Index] ), "@count", [count measure:] ), [@count] < 5 ), [@count] ) VAR _moreorequaltofivelist = SUMMARIZE ( FILTER ( VALUES ( product_dimension[product] ), [count measure:] >= 5 ), product_dimension[product] ) RETURN SWITCH ( TRUE (), SELECTEDVALUE ( product_dimension[product] ) = "other", _lessthanfiveall, SELECTEDVALUE ( product_dimension[product] ) IN _moreorequaltofivelist, [count measure:], NOT HASONEVALUE ( product_dimension[product] ), [count measure:] )

Re: Grouping products together by count less than 5 = other

Jay2022 — Fri, 27 Sep 2024 09:30:12 GMT

Thank you both

Re: Grouping products together by count less than 5 = other

Jay2022 — Fri, 27 Sep 2024 10:03:26 GMT

Thank you JIhwan,

Only I think your solution is perhaps more complex than I need. For example my table as a list of products and that is all) I can group by in the transform data section when i upload the data, therefore i am left with 2 columns Product and Count.

From here i just want to group any of those counted as less than 5 into 'Other' not sure i needed the table with product index etc

I'm hoping there may be a simpler solution