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 improbable weblog posts exhibiting you the best way to mimic the identical performance in DAX. The October launch of Energy BI Desktop is filled with new analytics options similar to Grouping, Binning and TOPN filtering. On high of that, one new superior function that’s not documented at time of writing this text, or at the least I haven’t discover something over the web, is “IN” operator in DAX. On this submit I present you the best way to use it in your DAX expressions.

Word 1: It’s essential to set up SSMS2016 to have the ability to write DAX queries supplied 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 nicely.

Word 2: When you run earlier variations of SQL Server it’s completely alright. There may be nothing particular in AdventureWorksDW2016CTP3 for this text that you just don’t get in older variations of the pattern database. However, take into account that SQL Server 2016 Developer Version is now free and you may obtain it very simply. Verify this out if you happen to’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”

Word: 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 the best way to join your Energy BI Desktop mannequin from completely 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 are able to 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:

Word: On the time of scripting this submit, 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 vitally simple to make use of “IN” operator somewhat than writing plenty of logical OR (||) operators. There are additionally different advanced eventualities that may be simplified utilizing “IN” operator in DAX.

In some instances 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 eventualities we are able to simply use SWITCH() operate. Within the following instance I create a gaggle of product classes as under:

If product class is “Clothes” or “Elements” then title it “Attire/Bike Components”. If product class is “Bikes” or “Equipment” then title 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 develop “FactResellerSales” and put “SalesAmount” on the chart. That is what we see:

It appears good isn’t it?

At the start of this submit 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 this point to help grouping.

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

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

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

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

• You’ll be able to double click on the group title and rename it if vital. I go away it as is for now
• Change the group title 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”
• Broaden “FactResellerSales” then add “SalesAmount” to the chart

As you see we created a gaggle of product classes utilizing Energy BI Desktop GUI. I chased the created group and I came upon 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 these days:

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.