topic Re: Counting Products based on Indicator in DAX Commands and Tips

Counting Products based on Indicator

pbi_girl_21 — Mon, 26 Feb 2024 19:27:11 GMT

Hi Everyone,

I have a complex calcuation I can't seem to figure out!

I have a table of data with grocery store names and products. The products are fruits, veggies, meat etc... but I only care about fruit. Here's an example:

Store	Product	Fruit Indicator
Store A	Beans
Store B	Strawberry	1
Store C	Lettuce
Store A	Strawberry	1
Store B	Banana	1
Store B	Strawberry	1

I am trying to identify which stores have no fruit, one fruit, or more than one. This also means I only want a unique count of each fruit type. The expect output is this:

Store	Unique Fruit Count
Store A	1
Store B	2
Store C	0

I have the below calculation which gives me my expected output:

Unique Fruit Count = CountRows(Filter ( Summmarize ( 'StoreData', 'StoreData'[Store], 'StoreData'[Product], 'StoreData'[Fruit Indicator]), 'StoreData'[Fruit Indicator] = 1 )) +0

However, this measure is giving me trouble when trying to work with it. I would like to now graph how many times fruits are part of a group of unique fruits per store. For example, in my above example I would graph Store B with strawberreis and banana on the x axis and each having a y axis value of 1 and 1 respectively. But Store A would not be included because it's unique fruit is count 1, Store C would not be included because it has no unique Fruit.

Does anybody have any advice? I'm really struggling to use my measure to graph by fruits that are in groups of more than 1 in my Unique Fruit Count.

Re: Counting Products based on Indicator

Anonymous — Tue, 27 Feb 2024 02:31:15 GMT

Hi pbi_girl_21

Try use filters to filter your unique fruit count field

Best Regards,

Jayleny

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

Re: Counting Products based on Indicator

pbi_girl_21 — Tue, 27 Feb 2024 15:46:49 GMT

Thank you, this works for isolating Store B.

But it also made me realize I forgot to ask a part of my question. The next step I want to do is to be able to isolate for examples like the strawberry in Store A. Specifically, if a fruit is the only unique fruit for a store, how can I identify it? Filtering for 1 in the example above doesn't seem to work for me, it only gives me blank results.

Re: Counting Products based on Indicator

Anonymous — Thu, 29 Feb 2024 01:55:58 GMT

Hi pbi_girl_21

Please try what I did in the screenshot.

Best Regards,

Jayleny

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

Re: Counting Products based on Indicator

talespin — Thu, 29 Feb 2024 07:34:52 GMT

hi pbi_girl_21

You can use these Measures.

Fruit Count =

VAR _FruitCount = CALCULATE(DISTINCTCOUNT(Fruits[Product]), Fruits[Fruit Indicator] = 1)

RETURN IF( ISBLANK(_FruitCount), 0 , _FruitCount)

Fruits =

VAR _FruitCount = [Fruit Count]

RETURN IF(_FruitCount = 1, CALCULATE( VALUES(Fruits[Product]), Fruits[Fruit Indicator] = 1) , BLANK())

You can also show all the fruits in a Store if that is the requirement.