cancel
Showing results for
Did you mean:
Highlighted
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 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.
1 ACCEPTED SOLUTION

Accepted Solutions
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
Super Contributor

## Re: Measure for a DistinctCount by Categories

Hi, try with this measure:

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

Regards

Victor

Lima - Peru

Proud to be a Datanaut!

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:

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?

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!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 298 members 3,114 guests
Recent signins: