In case you are a SQL man I guess you’ve used “IN” operator zillions of occasions. You may also appeared for a similar performance in DAX and I’m certain you’ve discovered improbable weblog posts displaying you the right way to mimic the identical performance in DAX. The October launch of Energy BI Desktop is stuffed with new analytics options corresponding to Grouping, Binning and TOPN filtering. On high of that, one new superior characteristic 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 the right way to use it in your DAX expressions.
Be aware 1: You should set up SSMS2016 to have the ability to write DAX queries offered on this article. Alternatively, you should use 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.
Be aware 2: In case you run earlier variations of SQL Server it’s completely alright. There’s nothing particular in AdventureWorksDW2016CTP3 for this text that you simply don’t get in older variations of the pattern database. However, remember that SQL Server 2016 Developer Version is now free and you’ll obtain it very simply. Verify this out should you’re to see how.
After downloading the most recent 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”
Be aware: The “XXXXXXXX” postfix is a random quantity.
- Open SSMS 2016 and hook up with Energy BI Desktop mannequin as an Evaluation Companies native server. Do you wish to be taught extra about the right way to join your Energy BI Desktop mannequin from totally different software program? Then test this out.
- Open an MDX new question
- Run the next DAX question
EVALUATE
SUMMARIZE('FactResellerSales'
, DimDate[CalendarYear]
, "Whole Reseller Gross sales"
, SUM('FactResellerSales'[SalesAmount])
)
Right here is the outcomes:
Now we wish to filter “CalendarYear” in order that the question reveals gross sales values for 2011 and 2012 solely. One frequent situation 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] , "Whole Reseller Gross sales" , sum(FactResellerSales[SalesAmount]) ) , DimDate[CalendarYear] = 2011 || DimDate[CalendarYear] = 2012 )
To any extent further we will write the above question utilizing “IN” operator in DAX like under:
EVALUATE FILTER( SUMMARIZE(FactResellerSales , DimDate[CalendarYear] , "Whole Reseller Gross sales" , sum(FactResellerSales[SalesAmount]) ) , DimDate[CalendarYear] IN (2011, 2012) )
Right here is the outcomes:
Be aware: On the time of penning this publish, the “IN” operator is NOT obtainable in any present model of SSAS 2016 Tabular mannequin (present model: 13.0.1601.5).
As you see it is extremely simple to make use of “IN” operator moderately than writing numerous logical OR (||) operators. There are additionally different complicated eventualities that may be simplified utilizing “IN” operator in DAX.
In some instances we wish to do grouping primarily based 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 will 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 situation 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 appears good isn’t it?
Originally 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 thus far to assist 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 possibly can double click on the group title and rename it if essential. 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 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.
