Tuesday, July 11, 2017

Power BI supports numeric range slicer now

This is not something released this month but March 2017. These new capabilities of Power BI Slicer is available but many do not use it because it is still under Preview and it has some limitations. But, let's see how useful it is and what sort of limitations it has.

As you see below, I have created a Power BI Report connecting with AdventureWorks2014 local database using DirectQuery mode. This is the view I used as the source;

USE AdventureWorks2014;
GO

CREATE VIEW dbo.SalesByCustomer
AS
SELECT 
      p.LastName + ' ' + p.FirstName Customer
   , t.Name
   , SUM([SubTotal]) Amount
  FROM [Sales].[SalesOrderHeader] s
 INNER JOIN Sales.Customer c
  ON s.CustomerID = c.CustomerID
 INNER JOIN Person.Person p
  ON p.BusinessEntityID = c.PersonID
 INNER JOIN Sales.SalesTerritory t
  ON s.TerritoryID = t.TerritoryID
GROUP BY p.LastName + ' ' + p.FirstName, t.Name;

The table of the report is created with Customer and Amount. Then two slicers are added, one using Amount and other user Name (Territory).


You will not see Numeric Range Filter unless you have enabled it. For enabling it, go to File -> Options and settings -> Options -> Preview features and check Numeric range slicer.


Once it is enabled, whenever a numeric value is dragged to a slicer, range slicer will be appeared automatically.


Multiple options such as Less than or equal to, Greater than or equal to are given with it for filtering based on values in the input boxes.

We can use this without any issue but you will face a limitation if you publish this to Power BI Service and view it.


As you see, this feature is still not available in Power BI Service. There are two more things to remember on this feature, 1) measures that are created with the model or measure in Analysis Services models cannot be used with this, 2) this filters row data that come from the source, not aggregated data shown in visuals.