cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JOO13 Regular Visitor
Regular Visitor

Measure for a DistinctCount by Categories

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
XX1AX
XX6AX
XX7AY
XX10AY
XX2BY
XX3BX
XX4BX
XX5BY
XX8CX
XX9CX

 

Desired output on 4th column: 

Order Product ID CustomersDistinct Count of ProdID x Customers 
XX1AX2
XX6AX2
XX7AY2
XX10AY2
XX2BY1
XX3BX3
XX4BX3
XX5BY3
XX8CX2
XX9CX2

 

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! 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
JOO13 Regular Visitor
Regular Visitor

Re: Measure for a DistinctCount by Categories

@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!

View solution in original post

3 REPLIES 3
Vvelarde Super Contributor
Super Contributor

Re: Measure for a DistinctCount by Categories

@JOO13 

 

Hi, try with this measure:

 

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

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




JOO13 Regular Visitor
Regular Visitor

Re: Measure for a DistinctCount by Categories

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:

@JOO13 

 

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? 

Highlighted
JOO13 Regular Visitor
Regular Visitor

Re: Measure for a DistinctCount by Categories

@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!

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)