Customer Count those purchased 1 of all pack

[Adnaniqb]

our company have 3 products and each product avaialble in 3 packs (100ML, 200ML, 300ML)

what I m trying do is to count the customers those purchased each 3 packs regardless of product. 

e.g.

customer 1 purchased each 3 pack of same product so PBI should count 1

customer 2 purchased each 3 product but only 1 pack so count = 0

hope my requirement is clear. 

below are 3 tables

and file Link

[johnt75 · Accepted solution]

Try

# customers bought all packs = 
VAR TotalPacks = COUNTROWS( ALL( 'Product'[Pack] ) )
VAR Result =
	COUNTROWS(
		FILTER(
			GROUPBY(
				SUMMARIZE(
					'Sales',
					'Customer'[Customer Code],
					'Product'[Pack]
				),
				'Customer'[Customer Code],
				"@num packs", SUMX( CURRENTGROUP( ), 1 )
			),
			[@num packs] = TotalPacks
		)
	)
RETURN
	Result

[Ahmedx · Accepted solution]

pls try this

Measure = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
           'Sales',
            Sales[Customer Code],
            "Count",
                COUNTROWS (
                    DISTINCT (
                        SUMMARIZE (
                            FILTER ( 'Sales', [Value] > 0 ),
                            Sales[Customer Code],
                            'Product'[Product Name]
                        )
                    )
                )
        ),
        [Count] = 3
    )
)

[ThxAlot]

Customer All Packs.pbix

[Adnaniqb]

Thanks for the reply,

actually there was mistake in my file as all customer bought all packs. but in this linked file i corrected it. 

if you see below screen shot except customer 2 all customers purchased all packs. hence customer count should be 5, 

also i will be gradeful if you explain how this dax query works. 

thanks in advance. 

[ThxAlot]

First of all, clarify which is the case you expect, Customer 1 or 2?

Case 1 is a bit more complex than 2.

[johnt75 · Accepted solution]

Try

# customers bought all packs = 
VAR TotalPacks = COUNTROWS( ALL( 'Product'[Pack] ) )
VAR Result =
	COUNTROWS(
		FILTER(
			GROUPBY(
				SUMMARIZE(
					'Sales',
					'Customer'[Customer Code],
					'Product'[Pack]
				),
				'Customer'[Customer Code],
				"@num packs", SUMX( CURRENTGROUP( ), 1 )
			),
			[@num packs] = TotalPacks
		)
	)
RETURN
	Result