title: Pivot table showing too many rows with DAX measure
body: <p>Consider the following two tables</p>
  <p><strong>Major</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Major Department</th>
  <th>Department Name</th>
  <th>Department Number</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Produce</td>
  <td>Produce</td>
  <td>2</td>
  </tr>
  <tr>
  <td>Produce</td>
  <td>Taxable Produce</td>
  <td>3</td>
  </tr>
  <tr>
  <td>Frozen</td>
  <td>Frozen</td>
  <td>5</td>
  </tr>
  <tr>
  <td>Grocery</td>
  <td>Grocery</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Grocery</td>
  <td>Taxable Grocery</td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>and <strong>Sales</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>UPC</th>
  <th>Department</th>
  <th>Category</th>
  <th>SubCategory</th>
  <th>Sales</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1125</td>
  <td>2</td>
  <td>Fruit</td>
  <td>Oranges</td>
  <td>20</td>
  </tr>
  <tr>
  <td>8256</td>
  <td>5</td>
  <td>Frozen Treats</td>
  <td>Fruit Bars</td>
  <td>15</td>
  </tr>
  <tr>
  <td>9230</td>
  <td>1</td>
  <td>Snacks</td>
  <td>Chips</td>
  <td>28</td>
  </tr>
  <tr>
  <td>4018</td>
  <td>2</td>
  <td>Fruit</td>
  <td>Bananas</td>
  <td>10</td>
  </tr>
  <tr>
  <td>925</td>
  <td>2</td>
  <td>Vegetables</td>
  <td>Onions</td>
  <td>9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>A relationship is created between the <em>Department</em> and <em>Department Number</em> columns.</p>
  <p><a href="https://i.stack.imgur.com/zuJNM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zuJNM.png" alt="Relationship between Sales and Major tables" /></a></p>
  <p>I create the following pivot table:</p>
  <p><a href="https://i.stack.imgur.com/N2Ti5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/N2Ti5.png" alt="Pivot Table with sales totals" /></a></p>
  <p>What I want to do is add a measure which shows the total for the <em>Major Department</em> on each row.</p>
  <p>I have tried <code>MajorDepartmentSales:=CALCULATE(SUM(Sales[Sales]),ALL(Sales[SubCategory]),ALL(Sales[Category]))</code>
  which should remove the filters on category and subcategory.  I would expect this to work, however it adds every category under the major department, but with the correct values.</p>
  <p><a href="https://i.stack.imgur.com/uh4Jv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uh4Jv.png" alt="Pivot table with extra rows" /></a></p>
  <p>Note, that the value of this measure is correct.  It shows the totals under that particular major department.  The problem is that it shows every category and subcategory under each major department whether they belong there or not.  Why is this?</p>
  <p>I have found two ways around this.  The first modifies the measure to <code>IF(COUNT(Sales[UPC])&gt;0,CALCULATE(SUM(Sales[Sales]),ALL(Sales[SubCategory]),ALL(Sales[Category])),BLANK())</code> which checks if there are any items under that particular row, and blanks it out otherwise.  The second method is to pull the major department to the <strong>Sales</strong> table with a calculated column <code>MajorDepartmentOnSales:=Related(Major[Major Department])</code> and then using this column in the pivot table instead of the major department from the <strong>Major</strong> table.</p>
  <p>Both produce what I want.  The <em>IF</em> method seems a bit sketchy to me, however.</p>
  <p><a href="https://i.stack.imgur.com/6rFDI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6rFDI.png" alt="Correct pivot table" /></a></p>
  <h2>Question</h2>
  <p>My question is then <em>why do I get these extra rows in the original approach</em>?</p>
  <p>It seems that DAX is correctly recognizing which major department is in play as it gets the value correct, but it is not recognizing that when it comes to filtering it out of the pivot table.  I am really new to DAX, and it seems that I am not understanding something either in how the relationship is propagated down or how power pivot interacts with the pivot table.</p>
  <p>How do I solve this?  Is there a way to rewrite the measure to not cause these extra rows, or do I have to use one of these alternative methods?  Ideally, I don't want to change the model.  (The actual data in the real report has more tables and more (slightly different) columns than this example, but the example recreates the essential issue.)</p>