topic Making Slicer on Multiple Categories with Counts in DAX Commands and Tips

Making Slicer on Multiple Categories with Counts

CuriousGuy001 — Fri, 24 May 2024 15:04:18 GMT

Hi Everyone,

I am trying to create a slicer with four selected options "Fruit" , "Vegetable", "Seafood", "Meat" by Count of how many was sold by our Sales Representatives. I got Product Category on row, and Sales Rep. on column. If I select Fruit it will show how many on count was sold by Jack as an example, Jack sold Apple and will count 1 and not counting 0 or empty cells.

Re: Making Slicer on Multiple Categories with Counts

xifeng_L — Sat, 25 May 2024 03:20:22 GMT

Hi CuriousGuy001 ,

First, you need to convert the data to a one-dimensional table.

Then, you can try below measure.

Measure = CALCULATE(COUNT('Table'[Value]), NOT 'Table'[Value] IN {"0",BLANK()})

Demo - Making Slicer on Multiple Categories with Counts.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Making Slicer on Multiple Categories with Counts

CuriousGuy001 — Mon, 27 May 2024 18:20:31 GMT

Hi Xifeng,

I like this method but it didn't work cause the are duplicate of product category, making more revenues. Thanks!

Re: Making Slicer on Multiple Categories with Counts

xifeng_L — Tue, 28 May 2024 10:07:33 GMT

Sorry, I didn't quite understand your question. If my previous demo doesn't match your expected result, you can tell me the correct result.

Re: Making Slicer on Multiple Categories with Counts

CuriousGuy001 — Wed, 29 May 2024 14:59:15 GMT

Hi xifeng,

Don't feel be sorry! so I have 385 data entry in the table, if I use that one dimension (Unpivot Other Column option) will make more data entry in the table, I will get 1540 data entry. I am trying to make a slicer for fruit, vegetable, seafood, meat that can give me a count of our sales rep that has been sold. The result will have to work with the 385 data entry I have, and the one dimensional option won't work.