7

I have a table like below:

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 get the DISTINCT COUNT of Brands by Country, which is simply:

C1 => 4
C2 => 6

How do I create a measure in Power BI that I can embed in a table or a tree-map? I have already tried this:

Measure = COUNTX(DISTINCT(sourceTable),sourceTable[Brand])

which returns total Brand count (not DISTINCT COUNT) per Country...

I have also tried below and it didn't work:

DistinctBrandCount =
    CALCULATE(DISTINCTCOUNT(SampleDataForDashboard[Brand]),
        GROUPBY(SampleDataForDashboard, SampleDataForDashboard[Country]))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
user1330974
  • 2,500
  • 5
  • 32
  • 60
  • 1
    I believe just creating a measure like so: Distinct Brtands := DISTINCTCOUNT(sourceTable[Brand]) and apply it to the country field should work – StelioK May 18 '18 at 15:08

1 Answers1

9

StelioK is correct, you can just use a measure like this:

DistinctBrands = DISTINCTCOUNT(sourceTable[Brand])

When you put the measure into a table with Country on the rows, the filter context passed to the measure only includes the country for that row.

Table

If you want to keep all of your other columns as well, you can write you measure like this:

DistinctBrands = CALCULATE(DISTINCTCOUNT(sourceTable[Brand]),
                     ALLEXCEPT(sourceTable, sourceTable[Country]))

This removes all filter context except for the Country and then calculates the distinct count.

enter image description here

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • it works! I swear when I tried `DISTINCTCOUNT` yesterday, it didn't work... I will post a follow-up question about how to make that number appear by default (not as a tooltip) in a Tree-map. Thank you for your help! – user1330974 May 18 '18 at 16:10