If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.

Requirements

• The latest version of Power BI Desktop (Current version is: 2.40.4554.463 64-bit (October 2016))
• SQL Server Management Studio 2016 (SSMS 2016)
• AdventureWorksDW sample database for SQL Server 2016

Note 1: You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.

Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.

Getting Started

After downloading the latest version of Power BI Desktop run it then

• “Get Data” from SQL Server
• From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
• Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:

“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”

Note: The “XXXXXXXX” postfix is a random number. 

• Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.

• Open an MDX new query
• Run the following DAX query

EVALUATE
    SUMMARIZE('FactResellerSales'
                , DimDate[CalendarYear]
                , "Total Reseller Sales"
                , SUM('FactResellerSales'[SalesAmount])
                )

Here is the results:

Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below:

EVALUATE
FILTER(SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) , DimDate[CalendarYear] = 2011 || DimDate[CalendarYear] = 2012
                    )

From now on we can write the above query using “IN” operator in DAX like below:

EVALUATE
    FILTER(
        SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) 
            , DimDate[CalendarYear] 
                IN (2011, 2012)
            )

Here is the results:

Note: At the time of writing this post, the “IN” operator is NOT available in any current version of SSAS 2016 Tabular model (current version: 13.0.1601.5).

As you see it is very easy to use “IN” operator rather than writing lots of logical OR (||) operators. There are also other complex scenarios that can be simplified using “IN” operator in DAX.

Using “IN” Operator in Calculated Columns in Power BI Desktop

In some cases we want to do grouping based on the values of a column. For instance, we might want to define groups of colours, groups of products or groups of years. For these scenarios we can easily use SWITCH() function. In the following example I create a group of product categories as below:

If product category is “Clothing” or “Components” then name it “Apparel/Bike Parts”. If product category is “Bikes” or “Accessories” then name it “Bikes/Accessories”.

We can implement the above scenario in a DAX expression like below:

Product Groups = SWITCH(TRUE()
                        , DimProductCategory[EnglishProductCategoryName] = "Clothing" || DimProductCategory[EnglishProductCategoryName] = "Components"
                        , "Apparel/Bike Parts"
                        , DimProductCategory[EnglishProductCategoryName] = "Bikes" || DimProductCategory[EnglishProductCategoryName] = "Accessories"
                        , "Bikes/Accessories"
                        )

Now lets add another calculated column using “IN” operator and SWITCH():

Product Groups (Using IN) = SWITCH(TRUE()
                        , DimProductCategory[EnglishProductCategoryName] IN ("Clothing", "Components")
                        , "Apparel/Bike Parts"
                        , DimProductCategory[EnglishProductCategoryName] IN ("Bikes", "Accessories")
                        , "Bikes/Accessories"
                        )

Now put a column chart on the page, then tick the new column we created. Then expand “FactResellerSales” and put “SalesAmount” on the chart. This is what we see:

It looks nice isn’t it?

Grouping in Power BI Desktop

At the beginning of this post I pointed to some new features added to Power BI Desktop in October release. From the new features, Grouping is very similar to the calculated column we created so far to support grouping.

Lets create a group in DimProductCategory table in Power BI Desktop.

• Expand DimProductCategory
• Right click on “EnglishProductCategoryName” and click “Group”

• Select “Accessories” and “Bikes” from “Ungrouped Values” then click “Group” button. To select both select one value then press Ctrl and click the next one

• Do the same for “Clothing” and “Components”

• You can double click the group name and rename it if necessary. I leave it as is for now
• Change the group name to “Product Category Group” then click OK

• A new column added to the “DimProductCategory” table

• Add another column chart to the page then tick “Product Category Group”
• Expand “FactResellerSales” then add “SalesAmount” to the chart

As you see we created a group of product categories using Power BI Desktop GUI. I chased the created group and I found out that it is indeed the same thing. The DAX expressions created behind the scene is very similar to what we used to create the calculated column in the previous steps. Here is the DAX expression that Power BI Desktop generated for the group column we created lately:

Product Category Group=SWITCH(
  TRUE,
  ISBLANK('DimProductCategory'[EnglishProductCategoryName]),
  "(Blank)",
  'DimProductCategory'[EnglishProductCategoryName] IN {"Accessories",
    "Bikes"},
  "Accessories & Bikes",
  'DimProductCategory'[EnglishProductCategoryName] IN {"Clothing",
    "Components"},
  "Clothing & Components",
  'DimProductCategory'[EnglishProductCategoryName]
)

Using “IN” operator in DAX not only simplifies writing DAX expressions, but also make the code more readable and more clear.