Measure for a DistinctCount by Categories

[Anonymous]

Hi Power BI Users, 

I have a table similar to below and trying to create a new measure by using DAX to get the distinct count of prodid&customers. In short, we are trying to calculate how many orders we have for each of the productID under each Customer.

Sampletable:

Order	ProductID	Customers
XX1	A	X
XX6	A	X
XX7	A	Y
XX10	A	Y
XX2	B	Y
XX3	B	X
XX4	B	X
XX5	B	Y
XX8	C	X
XX9	C	X

Desired output on 4th column: 

Order	Product ID	Customers	Distinct Count of ProdID x Customers
XX1	A	X	2
XX6	A	X	2
XX7	A	Y	2
XX10	A	Y	2
XX2	B	Y	1
XX3	B	X	3
XX4	B	X	3
XX5	B	Y	3
XX8	C	X	2
XX9	C	X	2

I've tried out with below DAX but it shown error below, 

Countitem = CALCULATE(distinctcount(Sampletable[Order]),(Sampletable[ProductId] && Sampletable[Customers]))

Error message: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression. 

Hope someone could shed some lights on this. 

Thanks in advance! 

[Vvelarde]

@Anonymous 

Hi, try with this measure:

DC Prod by Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order ] ),
    ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] )
)

Regards

Victor

[Anonymous]

Thanks, @Vvelarde . This works pretty well if we are not filtering the data. However, lets say if there is a date column and we would love to filter by date and check what is the distinctcount, what can be

---

@Vvelarde wrote:

@Anonymous 

Hi, try with this measure:

DC Prod by Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order ] ),
    ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] )
)

Regards

Victor

---

done in order to sync with the filters? 

[Anonymous · Accepted solution]

@Vvelarde, this has been resolved by added in "KEEPFILTERS".

DC Prod by Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order ] ),
    KEEPFILTERS(ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] ))
)

THANK YOU!