title: Power BI count instances of records that match criteria based on What-If parameter
body: <p>I have an issue trying to count rows in a Power BI table where the criteria varies based on a What-If parameter. The table is about 11 million rows long but I'll try to give a simple example of what I'm trying to do. Forgive the length of the post but there's actually not much too it.</p>
  
  <p>The data as it comes in from SQL Server looks something like this (simplified for clarity)</p>
  
  <pre><code>Item    Category    Price    AvgCatPrice    Variance
  Dog     Pet         20.00    15.00          33.33
  Cat     Pet         12.00    15.00          20.00
  Goat    Pet         18.00    15.00          20.00
  Maggot  Pet         05.00    15.00          66.67
  Apple   Fruit       01.00    01.20          16.67
  Orange  Fruit       01.20    01.20          00.00
  Mango   Fruit       01.30    01.20          08.33
  </code></pre>
  
  <p><em>The variance is simply the absolute difference between price and average for the category as a percentage but that's not relevant to the question</em></p>
  
  <p>So I load the data into Power Bi and everything looks like the above.</p>
  
  <p>I then add a simple calculated column as <code>Count =1</code>.</p>
  
  <p>If I put these values on a table with <code>Category</code> as rows and <code>Count</code> as Values I get </p>
  
  <pre><code>Category  Count
  Fruit       3
  Pet         4
  </code></pre>
  
  <p>This is as expected.</p>
  
  <p>I then added a what-if parameter called VarPc as a decimal number between 0 and 100 with increments of 5. I took the option to automatically add a slicer for the parameter.</p>
  
  <p>I then add a new column like this.</p>
  
  <pre><code>OverVarPc = IF(SOTestDat[Variance] &gt; VarPc[VarPc Value],1,0)
  </code></pre>
  
  <p>If I add this to the table I get </p>
  
  <pre><code>Category  Count  OverVarPc
  Fruit       3      2
  Pet         4      4
  </code></pre>
  
  <p>..which is correct assuming that VarPc is zero.</p>
  
  <p><strong>Now the first issue</strong> If I adjust the parameter via the slider or input box, the OverVarPc column is not reevaluated.</p>
  
  <p><strong>Next approach</strong> I added a second table, this time all detail is shown (no deliberate aggregation). 
  I then added a new measure to the <code>VarPc</code> table called <code>SumIfGt</code> with this as the definition</p>
  
  <pre><code>SumIFGt = IF((sum(SOTestDat[Variance]) - sum(VarPc[VarPc])) &gt;0,1,0)
  </code></pre>
  
  <p>The  table looks like this with the slider at 0%</p>
  
  <pre><code>Item    Price   Variance    Count   OverVarPc   SumIFGt
  Apple   1       16.67       1       1           1
  Cat     12      20          1       1           1
  Dog     20      33.33       1       1           1
  Goat    18      20          1       1           1
  Maggot  5       66.67       1       1           1
  Mango   1.3     8.33        1       1           1
  Orange  1.2     0           1       0           0
  </code></pre>
  
  <p>If I set it to 25% then the table now looks like this.</p>
  
  <pre><code>Item    Price   Variance    Count   OverVarPc   SumIFGt
  Apple   1       16.67       1       1           0
  Cat     12      20          1       1           0
  Dog     20      33.33       1       1           1
  Goat    18      20          1       1           0
  Maggot  5       66.67       1       1           1
  Mango   1.3     8.33        1       1           0
  Orange  1.2     0           1       0           0
  </code></pre>
  
  <p>Both results are as expected. </p>
  
  <p>So here's me thinking I'm good to go....
  <strong>Next Issue</strong> I drop this new measure onto my original aggregated table but the table now shows.</p>
  
  <pre><code>Category  Count  SumIfGt
  Fruit       3     0
  Pet         4     1
  </code></pre>
  
  <p>I sort of understand why it does this, it's aggregating <code>SUM</code> of <code>Variance</code> and (for fruit) getting 75 and then aggregating <code>VarPc</code> for each row (3 rows x 25) = 75 and then returning 0 as it's not greater than.</p>
  
  <p><strong>Finally the question!</strong> How can I get a simple count of the results of the measure. What I want to see in the final table is the Category, the total items in the category and the number of items whose variance is greater that the What-IF parameter.</p>
  
  <p>There are a bunch more things I want to do but I think if I get this cracked the rest will follow easily.</p>
  
  <p>Thanks for reading this far!</p>