title: IN operator + SWITCH
body: <p>There must be something very obvious that I'm missing here. I am using <code>SWITCH</code> function to create circa 20 bars on a bar chart that cumulate values from a different table.</p>
  
  <pre><code>SWITCH(
      VALUE(SELECTEDVALUE(Table1[RowNo])),
      1, CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"}),
      2, CALCULATE([m1] + [m2], Table2[Key] IN {"10-15", "15-20"}),
      3, CALCULATE([m1] + [m2], Table2[Key] IN {"20-25", "25-30"}),
      ...
  )
  </code></pre>
  
  <p>I thought that I will use <code>IN</code> operator (for the simplicity), instead of using a <strong>long version</strong> (example for the first bar):</p>
  
  <p><code>CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10")</code></p>
  
  <p>However, IN operator doesn't seem to work as I thought it would:</p>
  
  <p><code>CALCULATE([m1] + [m2], Table2[Key] = "0-5")</code> -> returns 10,000</p>
  
  <p><code>CALCULATE([m1] + [m2], Table2[Key] = "05-10")</code> -> returns 20,000</p>
  
  <p><strong>Long version</strong>:
  <code>CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10")</code> -> returns 30,000 (this is what I want)</p>
  
  <p><strong>Short version</strong> (with IN):
  <code>CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"})</code> -> returns 20,000 (incorrect; IN seems to always return the value for the second reference)</p>
  
  <p>Could you please help me adjusting my formula so that it returns the correct result (30,000)? I'm happy to provide some further information if needed.</p>
  
  <p><strong>Edit</strong>:
  here's an exemplary dataset - left table (Table1) and right table (Table2):
  <a href="https://i.stack.imgur.com/YJTz2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YJTz2.png" alt="enter image description here"></a></p>
  
  <p>In PowerBI, these two tables do not have any relationship in a Data Model. Table2 has two measures:
  <code>m1 = MAX(Table2[F])</code>
  <code>m2 = MAX(Table2[R])</code></p>
  
  <p>Table1 also has two measures (for comparison purposes):</p>
  
  <pre><code>mSwitch = 
      SWITCH(
          VALUE(SELECTEDVALUE(Table1[RowNo])),
          1, CALCULATE([m1] + [m2], Table2[Key] IN {"0-5", "05-10"}),
          2, CALCULATE([m1] + [m2], Table2[Key] IN {"10-15", "15-20"}),
          3, CALCULATE([m1] + [m2], Table2[Key] IN {"20-25", "25-30"})
      )
  
  mSwitch2 = 
      SWITCH(
          VALUE(SELECTEDVALUE(Table1[RowNo])),
          1, CALCULATE([m1] + [m2], Table2[Key] = "0-5") + CALCULATE([m1] + [m2], Table2[Key] = "05-10"),
          2, CALCULATE([m1] + [m2], Table2[Key] = "10-15") + CALCULATE([m1] + [m2], Table2[Key] = "15-20"),
          3, CALCULATE([m1] + [m2], Table2[Key] = "20-25") + CALCULATE([m1] + [m2], Table2[Key] = "25-30")
      )
  </code></pre>
  
  <p>As you can see on the screenshot below, there is a difference between mSwitch and mSwitch2 (mSwitch2 returning the correct results):</p>
  
  <p><a href="https://i.stack.imgur.com/0kW8F.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0kW8F.png" alt="enter image description here"></a></p>