This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:
CREATE PROCEDURE SP_Sales_by_Date
@date int
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey >= @date
END
GO
In Power BI Desktop, get data from SQL Server, then:
Enter Server name
Enter Database name
Select Data Connectivity Mode
Expand the Advanced options
Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
Click OK
Click Transform Data
Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:
I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:
Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:
Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:
let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
#"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
#"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
#"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
#"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
#"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
#"Removed Other Columns"
One of the most important aspects of the software development life cycle is to have control over different versions of a solution, especially in a project where there is more than one developer involved in the implementation. Just like when you normally create a project in visual studio and you commit the changes back to a source control system like GitHub or Azure DevOps, it’s advised to keep the history of different versions of your Power BI reports. What we expect from a source control solution is to keep tracking of all changes happening in the source code while developing a project. So you can easily roll back to a previous state if you like to.
The other benefit of having a source control process in place is when multiple developers are working on a single project. Every single one of them makes changes in the source code then they commit all the changes into the source control server without overwriting each others’ work.
With Power BI things are a bit different though. Power BI report files are PBIX files which are stored in binary format (well, PBIX is basically a zip file isn’t it?) which at the time of writing this post, there is no official way to enforce Power BI source control in any source control solutions like GitHub or Azure DevOps (YET).
Microsoft announced a fantastic feature last week (6/05/2020) named “Deployment Pipelines” which does exactly what we’re after, but it is currently a preview feature which is only available only to organisations with Power BI Premium. So it is out of the game for the majority of us.
Having said that, there is still a way to keep history of changes in the shape of different versions of PBIX files. This is called Version Control.
There are several ways you can enable version control over your PBIX files while developing the report. Regardless of the version control platform you need to think about having multiple environments and who can access them for doing what.
Environment
Accessible to
Description
Development
Developers
Data modellers and report writers access this environment for development purposes.
User Acceptance Test (UAT)
Developers, SMEs, Technical Leads, Power BI Admins
After the development is finished the developers deploy the solution to the UAT environment. The solution will then be tested by SMEs (Subject Matter Experts) to make sure the business requirements are met.
Pre-prod (Optional but recommended)
Technical Leads, Power BI Admins
After the solution passed all UAT testing scenarios Technical Leads or Power BI Admins will deploy it to Pre-prod for final checks to make sure all data sources are correctly pointing to production data sources and all reports and dashboards are working as expected.
Production
Technical Leads, Power BI Admins, End Users
After pre-prod checks completed Technical Leads or Power BI Admins deploy the solution to the Production environment which is then available to the end users.
Version Control Options
If your organisation does not have a Premium capacity then “Deployment Pipelines” feature is not available to you. So you need to come up with a solution though. In this section I name some Version Control options available to you
I was working on a project a wee bit ago that the customer had conditional formatting requirement on a Column Chart. They wanted to format the columns in the chart conditionally based on the average value based on the level of hierarchy you are at. Here is the scenario, I have a Calendar hierarchy as below:
Calendar Hierarchy:
Year
Semester
Quarter
Month
Day
I use “Adventure Works DW2017, Internet Sales” Excel as my source in Power BI Desktop. If I want to visualise “Total Sales” over the above “Calendar Hierarchy” I get something like this:
Now I activate “Average Line” from “Analytics” tab of the Line chart.
When I drill down in the line chart the Average line shows the average of that particular hierarchy level that I am in. This is quite cool that I get the average base on the level that I’m in code free.
Easy, right?
Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.
So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:
Detect which hierarchy level I am in
Calculate the average of sales for that particular hierarchy level
Change the colour of the columns that are below the average amount
Let’s get it done!
Detecting Hierarchy Level with ISINSCOPE() DAX Function
Microsoft introduced ISINSCOPE() DAX function in the November 2018 release of Power BI Desktop. Soon after the announcement “Kasper de Jonge” wrote a concise blogpost about it.
So I try to keep it as simple as possible. Here is how is works, the ISINSCOPE() function returns “True” when a specified column is in a level of a hierarchy. As stated earlier, we have a “Calendar Hierarchy” including the following 5 levels:
Year
Semester
Quarter
Month
Day
So, to determine if we are in each of the above hierarchy levels we just need to create DAX measures like below:
ISINSCOPE Year = ISINSCOPE('Date'[Year])
ISINSCOPE Semester = ISINSCOPE('Date'[Semester])
ISINSCOPE Quarter = ISINSCOPE('Date'[Quarter])
ISINSCOPE Month = ISINSCOPE('Date'[Month])
ISINSCOPE Day = ISINSCOPE('Date'[Day])
Now let’s do an easy experiment.
Put a Matrix on the canvas
Put the “Calendar Hierarchy” to “Rows”
Put the above measures in “Values”
As you see the “ISINSCOPE Year” shows “True” for the “Year” level. Let’s expand to the to the next level and see how the other measures work:
