IN – operator recognizing presence in a list of values (DAX – Power Pivot, Power BI)

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]);
    NOT ‘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.