Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kudy
Helper I
Helper I

Count People that have only bought Products shown in my slicer

So I have this table:

 

Kudy_2-1668006238971.png

 

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.

Kudy_0-1668007387395.png

 

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
johnt75
Super User
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 )

View solution in original post

3 REPLIES 3
johnt75
Super User
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 )

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors