Finding DISTINCT COUNT of Brands by Country

[plartoo]

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?

Thank you for your answers/suggestions!

[Greg_Deckler]

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])

[ChrisMendoza · Accepted solution]

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.

[plartoo]

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. 🙂