Utilizing “IN” Operator in DAX

In case you are a SQL man I wager you’ve used “IN” operator zillions of instances. You may additionally seemed for a similar performance in DAX and I’m positive you’ve discovered incredible weblog posts exhibiting you find out how to mimic the identical performance in DAX. The October launch of Energy BI Desktop is stuffed with new analytics options reminiscent of Grouping, Binning and TOPN filtering. On high of that, one new superior function that isn’t documented at time of writing this text, or no less than I haven’t discover something over the web, is “IN” operator in DAX. On this publish I present you find out how to use it in your DAX expressions.

Observe 1: You could set up SSMS2016 to have the ability to write DAX queries offered on this article. Alternatively, you should utilize DAX Studio . If for any causes you can not use SSMS 2016 or DAX Studio and also you solely have Energy BI Desktop, don’t fear, I’ll present some examples in Energy BI Desktop as effectively.

Observe 2: When you run earlier variations of SQL Server it’s completely alright. There’s nothing particular in AdventureWorksDW2016CTP3 for this text that you just don’t get in older variations of the pattern database. However, understand that SQL Server 2016 Developer Version is now free and you may obtain it very simply. Examine this out when you’re to see how.

After downloading the newest model of Energy BI Desktop run it then

• “Get Information” from SQL Server

• From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Energy BI Desktop mannequin

• Discover the native port of Energy BI Desktop by opening “msmdsrv.port.txt” file from the next path:

“%UserProfilepercentAppDataLocalMicrosoftPower BI DesktopAnalysisServicesWorkspacesAnalysisServicesWorkspaceXXXXXXXXInformation”

Observe: The “XXXXXXXX” postfix is a random quantity. 

• Open SSMS 2016 and connect with Energy BI Desktop mannequin as an Evaluation Companies native server. Do you wish to be taught extra about find out how to join your Energy BI Desktop mannequin from totally different software program? Then verify this out.

• Open an MDX new question
• Run the next DAX question

EVALUATE
    SUMMARIZE('FactResellerSales'
                , DimDate[CalendarYear]
                , "Complete Reseller Gross sales"
                , SUM('FactResellerSales'[SalesAmount])
                )

Right here is the outcomes:

Now we wish to filter “CalendarYear” in order that the question exhibits gross sales values for 2011 and 2012 solely. One frequent state of affairs we needed to do in prior variations of Energy BI Desktop, Energy Pivot or SSAS Tabular mannequin was to make use of a logical OR operator “||” like under:

EVALUATE
FILTER(SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Complete Reseller Gross sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) , DimDate[CalendarYear] = 2011 || DimDate[CalendarYear] = 2012
                    )

Any more we will write the above question utilizing “IN” operator in DAX like under:

EVALUATE
    FILTER(
        SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Complete Reseller Gross sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) 
            , DimDate[CalendarYear] 
                IN (2011, 2012)
            )

Right here is the outcomes:

Observe: On the time of penning this publish, the “IN” operator is NOT accessible in any present model of SSAS 2016 Tabular mannequin (present model: 13.0.1601.5).

As you see it is extremely straightforward to make use of “IN” operator relatively than writing numerous logical OR (||) operators. There are additionally different advanced situations that may be simplified utilizing “IN” operator in DAX.

In some circumstances we wish to do grouping based mostly on the values of a column. As an illustration, we would wish to outline teams of colors, teams of merchandise or teams of years. For these situations we will simply use SWITCH() perform. Within the following instance I create a bunch of product classes as under:

If product class is “Clothes” or “Elements” then identify it “Attire/Bike Components”. If product class is “Bikes” or “Equipment” then identify it “Bikes/Equipment”.

We will implement the above state of affairs in a DAX expression like under:

Product Teams = SWITCH(TRUE()
                        , DimProductCategory[EnglishProductCategoryName] = "Clothes" || DimProductCategory[EnglishProductCategoryName] = "Elements"
                        , "Attire/Bike Components"
                        , DimProductCategory[EnglishProductCategoryName] = "Bikes" || DimProductCategory[EnglishProductCategoryName] = "Equipment"
                        , "Bikes/Equipment"
                        )

Now lets add one other calculated column utilizing “IN” operator and SWITCH():

Product Teams (Utilizing IN) = SWITCH(TRUE()
                        , DimProductCategory[EnglishProductCategoryName] IN ("Clothes", "Elements")
                        , "Attire/Bike Components"
                        , DimProductCategory[EnglishProductCategoryName] IN ("Bikes", "Equipment")
                        , "Bikes/Equipment"
                        )

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

It seems good isn’t it?

In the beginning of this publish I pointed to some new options added to Energy BI Desktop in October launch. From the brand new options, Grouping is similar to the calculated column we created to date to assist grouping.

Lets create a bunch in DimProductCategory desk in Energy BI Desktop.

• Develop DimProductCategory
• Proper click on on “EnglishProductCategoryName” and click on “Group”

• Choose “Equipment” and “Bikes” from “Ungrouped Values” then click on “Group” button. To pick each choose one worth then press Ctrl and click on the subsequent one

• Do the identical for “Clothes” and “Elements”

• You may double click on the group identify and rename it if needed. I go away it as is for now
• Change the group identify to “Product Class Group” then click on OK

• A brand new column added to the “DimProductCategory” desk

• Add one other column chart to the web page then tick “Product Class Group”
• Develop “FactResellerSales” then add “SalesAmount” to the chart

As you see we created a bunch of product classes utilizing Energy BI Desktop GUI. I chased the created group and I discovered that it’s certainly the identical factor. The DAX expressions created behind the scene is similar to what we used to create the calculated column within the earlier steps. Right here is the DAX expression that Energy BI Desktop generated for the group column we created recently:

Product Class Group=SWITCH(
  TRUE,
  ISBLANK('DimProductCategory'[EnglishProductCategoryName]),
  "(Clean)",
  'DimProductCategory'[EnglishProductCategoryName] IN {"Equipment",
    "Bikes"},
  "Equipment & Bikes",
  'DimProductCategory'[EnglishProductCategoryName] IN {"Clothes",
    "Elements"},
  "Clothes & Elements",
  'DimProductCategory'[EnglishProductCategoryName]
)

Utilizing “IN” operator in DAX not solely simplifies writing DAX expressions, but additionally make the code extra readable and extra clear.