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

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? 

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!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 61 members 1,311 guests
Please welcome our newest community members: