DAX - filtering out whole row from data table that meets certain condition(s)

Question

I have a table like following:

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-baqh{text-align:center;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-baqh">Store Name</th>
    <th class="tg-baqh">Week Number</th>
    <th class="tg-baqh">Sales</th>
    <th class="tg-baqh">Sales-LY</th>
  </tr>
  <tr>
    <td class="tg-baqh">Store A</td>
    <td class="tg-baqh">1</td>
    <td class="tg-baqh">20</td>
    <td class="tg-baqh">15</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store A</td>
    <td class="tg-baqh">2</td>
    <td class="tg-baqh">25</td>
    <td class="tg-baqh">20</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store A</td>
    <td class="tg-baqh">3</td>
    <td class="tg-baqh">30</td>
    <td class="tg-baqh">25</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store B</td>
    <td class="tg-baqh">1</td>
    <td class="tg-baqh">15</td>
    <td class="tg-baqh">10</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store B</td>
    <td class="tg-baqh">2</td>
    <td class="tg-baqh">15</td>
    <td class="tg-baqh">15</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store B</td>
    <td class="tg-baqh">3</td>
    <td class="tg-baqh">20</td>
    <td class="tg-baqh">15</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store C</td>
    <td class="tg-baqh">1</td>
    <td class="tg-baqh">30</td>
    <td class="tg-baqh">25</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store C</td>
    <td class="tg-baqh">2</td>
    <td class="tg-baqh">0</td>
    <td class="tg-baqh">20</td>
  </tr>
  <tr>
    <td class="tg-baqh">Store C</td>
    <td class="tg-baqh">3</td>
    <td class="tg-baqh">25</td>
    <td class="tg-baqh">20</td>
  </tr>
</table>

I would like to return (lets' say) a pivot table with

Salex IDX = SUM(Sales)/SUM(Sales-LY) as a measure, ignoring the data points for "Week 2 for Store C". So it's not a filter on just Week Number or Store, but its a filter on specific row(s) identified by multiple parameters.

Essentially, i would like to get 'like for like' results, excluding any weeks where Sales or Sales-LY columns are zero(or null)

Any ideas?

I think the solution is something using CALCULATETABLE but couldn't find a solution that works yet. — Mari

Mike Honey Mike Honey · Accepted Answer · 2016-06-21T01:37:14

I would add a calculated column to concatenate Store and Week, something like this:

=[Store Name]&", Week: "&[Week Number]

Then you can use a filter or slicer to exclude the Store & Week combination you want.

DAX - filtering out whole row from data table that meets certain condition(s)

1 Answers