title: power bi: Aggregation of Distinct Count Measure
body: <p>I need to count how many teams have only one associated city with at least one employee. My data set has many columns, but the relevant ones for this case are the following:</p>
  
  <pre><code>Team    City    Employees
  A      Shanghai    3
  A      Beijing     5
  B      Helsinki    1
  B      Beijing     0
  C      Berlin      10
  D      Shanghai    5
  D      Berlin      6
  </code></pre>
  
  <p>In the example above this count would be 2 (<code>B</code> and <code>C</code>), and I managed to create a measure that counts how many associated <code>cities</code> each <code>team</code> has using:</p>
  
  <pre><code># of Cities = CALCULATE(DISTINCTCOUNT(Data[City]);FILTER('Data';Data'[Employee]&gt;0))
  </code></pre>
  
  <p>In order to check which teams have employees in only one city I tried to use the simple measure:</p>
  
  <pre><code>Total Team w/ one city = IF( [# of Cities] = 1 ; 1 ; 0 ) 
  </code></pre>
  
  <p>but that is not aggregate-able.</p>
  
  <p>Any ideas on how to do it? I'd prefer to have it as a measure instead of new calculated column so I can slice the results according to other columns in the dataset.</p>
  
  <p>Thanks in advance!</p>