The IN operator in a calculation includes only these items, that can be found in a list of items.

Let´s say there is a table with cars, where we´d like to calculate the total price for blue and red cars. Then we can use this formula:

  • Blue and red cars price =
       CALCULATE(SUM('list of cars'[Price]);
       'list of cars'[Color] IN {"blue";"red"})

Of course, it also works negatively. To include all cars except blue and red, you can change it to this:

  • Price of cars except blue and red = 
       CALCULATE(sum('list of cars'[Price]);
       'list of cars'[Color] IN {"blue";"red"})

In DAX, you can often use many ways to get the same result. So you can replace IN by CONTAINSROW:

  • Blue and red cars price = 
       CALCULATE(sum('list of cars'[Price]);
       CONTAINSROW ({ "blue";"red" };'list of cars'[Color]))

or by FILTER.

Leave a Reply

Your email address will not be published.

*

clear formPost comment