Count customers over dimension attributes (that fulfill criterias)

[Anonymous]

Hi. **See example table at the bottom to easier understand**

I want to calculate distinct nr of Customers (Id) over Product-category.
Customer Id can occur several times (if we have for example one customer that has bought several products within same product category). There are 3 product categories. I want to calculate the following:
Measure 1: Distinct nr Customer_Id that has bought only one product in each category and it has both something from all of the 3 categories.
Measure 2: Nr distinct customer that has blank values on all the product categories connected to the Customer_Id. 
Measure 3: Nr distinct customer where some Category are blanks but not all.
Measure 4: Nr distinct customer that have some of the categories that appear more than once.

I have tried different DAX-measures as well as making a Summary-table where I assign a INT-value to the category attributes and then for measure 1; Measure 1: CALCULATE(DISTINCTCOUNTNOBLANK('Fact (2)'[Reservation_Id]),Filter('Fact (2)','Fact (2)'[ReservationsGrouped]=6))
Note: (cat 1: I assigned value:1, cat: 2 - 2, cat3: value 3 = 6).

It gives reasonable results but doesnt handle problems like there might be duplicates from same category and no value for another category that still could sum up to 6 without fulfilling criteria for measure 1. Anyone that now how to deal with this, should be an easier way to deal with counting over different dimension attributes based on criterias.

I cannot provide the real data since it is sensitive customer data but see example table: 
In this table: Reservation_Id = 1, would be counted in Measure 2
Reservation_Id= 2, Measure 1
Id=3, Measure 4
Id=4, Measure 3

Reservation_Id	Category	Product
1
1
2	A	Fish
2	B	Chips
2	C	Fanta
3	A	Fish
3	A	Fish
4
4	A	Fish

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please use the following measure:

countblank category = COUNTBLANK('Table'[Category])
countcategorywithnoblank = COUNTX(FILTER('Table','Table'[Category]<>BLANK()),'Table'[Category])
distinctcountcategory = CALCULATE(DISTINCTCOUNT('Table'[Category]),FILTER(VALUES('Table'[Category]),'Table'[Category]<>BLANK())) 

Measure1 = VAR COUNTCA = [distinctcountcategory] return CALCULATE(DISTINCTCOUNT('Table'[Reservation_Id]),FILTER(VALUES('Table'[Reservation_Id]),[countcategorywithnoblank] = COUNTCA &&[distinctcountcategory] = COUNTCA))

Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Reservation_Id]),FILTER(VALUES('Table'[Reservation_Id]),[countcategorywithnoblank] = 0))

Measure3 = VAR COUNTCA = [distinctcountcategory] return CALCULATE(DISTINCTCOUNT('Table'[Reservation_Id]),FILTER(VALUES('Table'[Reservation_Id]),[countcategorywithnoblank] < COUNTCA &&[countcategorywithnoblank]>0 &&[countblank category]>0))

Measure4 = CALCULATE(DISTINCTCOUNT('Table'[Reservation_Id]),FILTER(VALUES('Table'[Reservation_Id]),[countcategorywithnoblank]>[distinctcountcategory]))

Please also refer to the pbix file.

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

Best Regards,

Dedmon Dai