If you’re a SQL man I guess you’ve used “IN” operator zillions of occasions. You may also seemed for a similar performance in DAX and I’m certain you’ve discovered implausible weblog posts displaying you find out how to mimic the identical performance in DAX. The October launch of Energy BI Desktop is filled with new analytics options corresponding 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 no less than I haven’t discover something over the web, is “IN” operator in DAX. On this put up I present you find out how to use it in your DAX expressions.
Notice 1: It’s essential to 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’t 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.
Notice 2: For those who 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. Verify this out in case 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”
Notice: The “XXXXXXXX” postfix is a random quantity.
- Open SSMS 2016 and hook up with Energy BI Desktop mannequin as an Evaluation Providers native server. Do you need to be taught extra about find out how to join your Energy BI Desktop mannequin from completely different software program? Then examine 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 need to filter “CalendarYear” in order that the question exhibits gross sales values for 2011 and 2012 solely. One widespread 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 )
To any extent further 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:
Notice: On the time of scripting this put up, 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 quite than writing a lot of logical OR (||) operators. There are additionally different complicated eventualities that may be simplified utilizing “IN” operator in DAX.
In some instances we need to do grouping primarily based on the values of a column. As an example, we’d need to outline teams of colors, teams of merchandise or teams of years. For these eventualities we are able to simply use SWITCH() perform. 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 Elements”. If product class is “Bikes” or “Equipment” then title it “Bikes/Equipment”.
We are able to implement the above state of affairs in a DAX expression like under:
Product Teams = SWITCH(TRUE() , DimProductCategory[EnglishProductCategoryName] = "Clothes" || DimProductCategory[EnglishProductCategoryName] = "Elements" , "Attire/Bike Elements" , 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 Elements"
, 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 first of this put up 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 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 each choose one worth then press Ctrl and click on the following one
- Do the identical for “Clothes” and “Elements”
- You possibly can double click on the group title and rename it if needed. I depart 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 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 in addition make the code extra readable and extra clear.
