cancel
Showing results for
Did you mean:
Frequent Visitor

## Finding DISTINCT COUNT of Brands by Country

Hi all,

I have a table like this:

```Country, Advertiser, Brand, Spend
C1, A1, B1, 10
C1, A1, B2, 5
C1, A2, B3, 0
C1, A2, B4, 20
C2, A1, B1, 8
C2, A1, B5, 7
C2, A2, B6, 4
C2, A2, B3, 3
C2, A2, B7, 2
C2, A3, B8, 9```

I'd like to find the DISTINCT COUNT of Brands by Country as below:

```C1 => 4
C2 => 6```

How do I create a Measure in Power BI Desktop to achieve this?

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

## Re: Finding DISTINCT COUNT of Brands by Country

hello @plartoo,

Possibly I'm over simplifying your problem however I've accomplished your desired output with 2 different measures:

```Measure = CALCULATE(COUNTROWS(Table3),DISTINCT(Table3[Country]))

Measure 2 = COUNTROWS(Table3)```

Also I used Count of Brand in the Values area as well.

`Measure 3 = CALCULATE(DISTINCTCOUNT(Table3[Brand]), GROUPBY(Table3,Table3[Country]))`

Was taken from https://stackoverflow.com/questions/50414271/creating-a-measure-that-returns-distinct-count-by-count... which looks exactly like your question.

DISTINCTCOUNT in Measure 3 is returning 8 because 'B1' & 'B3' is only counted once over the entire data set.

3 REPLIES 3
Senior Member

## Re: Finding DISTINCT COUNT of Brands by Country

hello @plartoo,

Possibly I'm over simplifying your problem however I've accomplished your desired output with 2 different measures:

```Measure = CALCULATE(COUNTROWS(Table3),DISTINCT(Table3[Country]))

Measure 2 = COUNTROWS(Table3)```

Also I used Count of Brand in the Values area as well.

`Measure 3 = CALCULATE(DISTINCTCOUNT(Table3[Brand]), GROUPBY(Table3,Table3[Country]))`

Was taken from https://stackoverflow.com/questions/50414271/creating-a-measure-that-returns-distinct-count-by-count... which looks exactly like your question.

DISTINCTCOUNT in Measure 3 is returning 8 because 'B1' & 'B3' is only counted once over the entire data set.

Super User

## Re: Finding DISTINCT COUNT of Brands by Country

Here is a single measure solution. Put this in a table along with Country.

```Measure =
VAR __tmpTable = SUMMARIZE(brands,brands[Country],"__Brands",DISTINCTCOUNT(brands[ Brand]))
RETURN MAXX(__tmpTable,[__Brands])```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

## Re: Finding DISTINCT COUNT of Brands by Country

Thank you, Dozer. This solution works! I'll ask another follow-up question about how to make this distinct count value appear in a Tree-map soon.

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: 451 members 4,651 guests
Recent signins: