title: PowerBI DAX: Creating Measure which accepts partial visual filters from same table
body: <p>I have a Fact table and a Calendar table. The calendar table has a column boolean column denoting if a day is a holiday.</p>
  
  <p><a href="https://i.stack.imgur.com/Pvs9e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pvs9e.png" alt="Relationship"></a></p>
  
  <p>I wan't to create a measure: AllSales which ignores any filters on Holiday but respects filters on all other Calendar columns. See later for applied visual level filters.</p>
  
  <p>The measure below works except for total sums:</p>
  
  <pre><code>AllSales = CALCULATE(SUM(FactSales[Sales]); ALL('Calendar'[Holiday]); ALL('Calendar'[DateKey]))
  </code></pre>
  
  <p>See below image for the result. Individual weeks are OK as those are ok, but notice the years that sum to the wrong total. The Bar chat displays correctly but the totals in the matrix table are wrong. The total of AllSales should be 700 due to the visual level filter <code>Calendar[DateKey]</code> that removes the week one of 2019, yet it is still counted in the total.</p>
  
  <p><a href="https://i.stack.imgur.com/j74oL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j74oL.png" alt="Wrong Result"></a></p>
  
  <p>So to sum up:</p>
  
  <ul>
  <li>Visual level filter on <code>Calendar[DateKey] &gt; "2019 year 01 week</code></li>
  <li>Visual level filter on <code>Calendar[Holiday] = False</code></li>
  <li>Measure AllSales should respect DateKey filter</li>
  <li>Measure AllSales should ignore all Holiday filters</li>
  <li>Visuals are sorted by column <code>Calendar[Year], Calendar[Week]</code></li>
  </ul>
  
  <p>If i remove the <code>ALL(Calendar[DateKey])</code> its like my <code>ALL(Calendar[Holiday])</code> filter is just ignored and <code>AllSales</code> becomes equivalent to <code>Sales</code> which is the raw data column.</p>
  
  <p>If you wan't to play with the data here are the two M queries to create Calendar and FactSales tables. Just link them by DateKey 1:* one directional.</p>
  
  <p>Create two new queries and paste below code into each in the Advanced Editor(M language editor)</p>
  
  <p>Calendar query:</p>
  
  <pre><code>let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAQiJR0wB0iBmCFFpalKsTpICoxQFbgl5hSjqTAmqMIEocIIuwpT3CqMDJAcamSA6VCoAiNUBViMMCaowgShAoczTLGqiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, year = _t, Week = _t, Holiday = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"year", Int64.Type}, {"Week", Int64.Type}, {"Holiday", type logical}}),
      #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"year", "Year"}})
  in
      #"Renamed Columns"
  </code></pre>
  
  <p>FactSales query:</p>
  
  <pre><code>let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIwtNQ1MAQipVgdDEEjbILG2ARNsAmaoggaGWCxCCpohE3QGJugCTZBoEWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t, DateKey = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"DateKey", type date}})
  in
      #"Changed Type"
  </code></pre>
  
  <p>I've come to the conclusion that DAX/TabularModel do not really work well with multiple slicers/filters from the same table and that i should split them up. I would still like to know if someone has a DAX solution to this problem.</p>