cancel
Showing results for
Did you mean:
Highlighted
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
Established 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
Established 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])```