This is the second article in which I cover different use cases for using SQL Server Table-Valued Functions with Power BI.
In the previous article I showed you how a table valued function can be used to hide lower levels of a hierarchy based on user id. This is handy if you need to prevent certain users from breaking down aggregate measures while interacting with a report or dashboard in Power BI.
In this article I will show you how you can use table valued functions to filter the results based on a date range stored in your database and allow the report author to control how many years of data to bring back with an optional input parameter.
- SQL Server 2016 or later. You can download the SQL Server 2017 developer edition HERE
- Wide World Importer sample database. A copy can be found HERE
- Power BI Desktop. You can download the latest version from HERE
- SQL Server management Studio or Azure Data Studio
Use case 2: Change results based on time
This use case comes in handy if you want your results to be filtered based on a date and time in your database and not on the date time of your Power BI users. This situation may occur in a globally distributed system in which Power BI users and the database they are querying are located in different time zones or in situations where the data lags behind the current date/time of your users. I will cover a situation where you want the data set to only show data up to a point in time stored in your database.
Using the Wide World Importers sample database suppose you wanted to limit the orders that a person can report on to the last year in which there were orders in the database. Run the following query.
USE WideWorldImportersDW GO SELECT MIN([Order Date Key]) AS [Earliest Order Date] , MAX([Order Date Key]) AS [Latetest Order Date] FROM WideWorldImportersDW.Fact.[Order] GO
As you can see from the results, we have orders from 2013 up to 2016. If we were to use the Power BI relative date slicer and set it to only show data from the past 1 year we would not see any results because the Power BI relative date slicer is based on today’s date April 6, 2019 and not on a date in the database. One way to overcome this problem is to use SQL Server Table-valued functions and encapsulate the logic to only show orders from June 1 2015 to May 31 2016. To do this will create a new function using the code below.
Run the following code in the Wide World Importers database to create a new function. We are including an optional parameter so that the report author can change how many years they want to go back when they connect to the data.
--DROP FUNCTION dbo.ufn_Orders_PastYear --GO CREATE FUNCTION dbo.ufn_Orders_PastYear(@NumberOfYears INT = NULL) RETURNSTABLE AS RETURN ( SELECT * FROM WideWorldImportersDW.Fact.[Order] ord WHERE ord.[Order Date Key] BETWEEN (SELECT DATEADD(year, -ISNULL(@NumberOfYears, 1), MAX([Order Date Key])) FROM WideWorldImportersDW.Fact.[Order]) AND (SELECT MAX([Order Date Key]) FROM WideWorldImportersDW.Fact.[Order]) ); GO
Notice that I negate the number of years by adding a negative sign in front of the ISNULL function in the select statement, this is to simplify the report authoring experience with using this function. Next, we will make a DirectQuery connection to the function using Power BI.
Connect with Power BI
Similar to before connect to the SQL Server database using a DirectQuery connection and select the function ufn_Orders_PastYear from the list of database objects.
As you can see in the image above the function parameter @NumberOfYears appears in Power BI as an optional parameter. If you leave it blank and click apply it will pull back 1 year’s worth of data based on what is available in the database. You can enter in your own number to control how many years back you query the Orders fact table. Incorporating parameters is a very powerful way to give the report author control of the results.
Once the data has been loaded in let’s visualize it using a simple bar chart.
Your results should look like the following image below.
As you can see in the chart we only have data from 2015 to 2016. To make the chart a bit easier to read lets add a proper date hierarchy. We will need to build it because we are using a DirectQuery to access the data so the autogenerated date hierarchies are not available, those are only created when you import data into Power BI and set the data type to be a date.
Create Year Column
Create a new calculated column and use the following DAX code to pull out the year value from the Order Date Key field.
OrderYear = Year([Order Date Key])
Create Month Columns
Next, we will create two month columns one will be used to sort and the other will be used to display the month name on the report.
Use the following DAX code to create a new month number calculated column.
OrderMonth = Month([Order Date Key])
Now create a new calculated column to store the month name using the following DAX code.
Order Month Name = SWITCH ( [OrderMonth], 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December" )
Now we need to set the sort by column property of the Order Month Name to use the value of the OrderMonth column. To do this navigate to the model viewer and click on the Order Month Name field and then set the Sort by column to OrderMonth.
Now we will create a new Hierarchy based on OrderYear and Order Month Name.
Click on the chart and the Axis value with the new hierarchy we just created. Drill down a level to see the years and months.
As you can see this makes the chart much easier to read. Now lets insert some new data into the table and refresh the report.
Add some data
Run the following SQL to create new Date and Order records.
Refresh the report and notice how it updates so that it only has one column.
This is because there is only 1 record from March 2018 until April 2019.
Combining SQL Server database functions with Power BI is a powerful way to abstract and encapsulate logic in the database thus simplifying the report authors job and ensuring the right data is presented to report consumers.
Hopefully you have found this to be another practical post.
Until next time.