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 I

## Count People that have only bought Products shown in my slicer

So I have this table:

And I have the slicer below where I chose 2 products out of 3.

What I want to see is the number of names who only bought the products in my slicer, and nothing else.

I just want the number of names as shown in blue. (actual names inside the parenthesis "( )" are not required).

Explanation of wished measure:

Apple = 1 / because it was only Sam who has only apple under his name;

Banana = 0 / because there is no person who has only banana;

Total = 2 / because I am asking a question: How many people has bought the products shown in my slicer, and nothing else

so = 2 means Sam (only apple) and Anna (apple and banana)

"Alex" is not in the list because he has purchased a product which is not chosen in my slicer.

I hope i was clear with explanation.

1 ACCEPTED SOLUTION
Super User

You can create a measure like

``````Bought only these products =
VAR ChosenProducts = VALUES( 'Sales'[Product] )
VAR OtherProducts =
EXCEPT( ALL( 'Sales'[Product] ), ChosenProducts )
VAR BoughtChosenProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), ChosenProducts )
VAR BoughtOtherProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), OtherProducts )
VAR Result =
COUNTROWS(
EXCEPT( BoughtChosenProducts, BoughtOtherProducts )
)
RETURN
COALESCE( Result, 0 )``````
3 REPLIES 3
Super User

You can create a measure like

``````Bought only these products =
VAR ChosenProducts = VALUES( 'Sales'[Product] )
VAR OtherProducts =
EXCEPT( ALL( 'Sales'[Product] ), ChosenProducts )
VAR BoughtChosenProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), ChosenProducts )
VAR BoughtOtherProducts =
CALCULATETABLE( VALUES( 'Sales'[Name] ), OtherProducts )
VAR Result =
COUNTROWS(
EXCEPT( BoughtChosenProducts, BoughtOtherProducts )
)
RETURN
COALESCE( Result, 0 )``````
Helper I

WOW,

it works as wished.

New issue i didnot think of  - speed of the measure. It takes long time when applied on a table with many rows.
Is diving your code into two measures would be a good solution? Or what would you recommend?

Super User

It depends on how dynamic you need it to be. If you need to be able to slice by date period or some other variable then I'm not sure how it could be speeded up. However, if you only need to consider the entire sales table you could create a calculated table containing all the customers and all the products they have ever bought, e.g.

``````Customers Products =
GENERATE (
ALLNOBLANKROW ( 'Sales'[Name] ),
CALCULATETABLE ( VALUES ( 'Sales'[Product] ) )
)
``````

You could then change my original code to work from this table instead of the Sales table

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.