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
- The latest version of Power BI Desktop (Current version is: 2.40.4554.463 64-bit (October 2016))
- SQL Server Management Studio 2016 (SSMS 2016)
- AdventureWorksDW sample database for SQL Server 2016
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.
- 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:
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:
