Using “IN” Operator in DAX

IN operator in DAX

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

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.

SSMS Connect to Power BI Desktop Model

  • 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:

Writing DAX in SSMS

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:

IN operator in DAX

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"
                        )

OR Logical Operator in DAX

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"
                        )

IN Operator in DAX

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:

Grouping in Power BI Desktop

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”

Create Groups in Power BI Desktop

  • 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

Create Groups in Power BI Desktop

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

Create Groups in Power BI Desktop

  • 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

Create Groups in Power BI Desktop

  • A new column added to the “DimProductCategory” table

Create Groups in Power BI Desktop

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

Use Groups in Power BI Desktop

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.


Discover more from BI Insight

Subscribe to get the latest posts sent to your email.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.