In my last blog post, Getting Started with Power BI Desktop and SCCM, I showed you how to create a very basic Power BI dashboard by adding a table to the canvas. In this post I’ll show you how to add a calculated column to a Power BI table. Ultimately this particular calculated column will be used within a chart, but I’ll talk about that more in my next blog post.
Keep in mind that the steps I’ll be showing in this post can be used for any number of items that need to be calculated. This can include calculating the age of computers or determining how long ago a software title was last used.
In my last post I started designing a Power BI dashboard about computer ages by adding a table. As a reminder this is what the Power BI canvas looked like at the end of that post.
Adding the Calculated Column to the Query
Before adding a chart to the dashboard, I first needed to determine the age of a computer. Luckily I had the warranty start dates for all of my computers, so I could figure out their ages by using the following formula.
Today’s Date – Warranty Date = Age of the Computer
I could either modify my existing SQL Server query or I could update the query within Power BI. Normally I would modify my existing SQL Server query, but you may not have that kind of time or option, so I’ll show you what I did within Power BI.
The first two things that I needed to consider were:
-How do you get today’s date?
-How do you calculate the difference between two dates?
In order to get today’s date I simply used the Power BI function called: NOW()
After doing a quick search, I found that DateDiff() is the function for calculating the difference between two dates.
Note: DateDiff within Power BI has a different order for the parameters compared to its SQL Server equivalent.
The DateDiff function takes three parameters:
-Time Frame (Days, Months, Years, etc.)
This makes the equation to determine a computer’s age as follows:
DATEDIFF(Query1[Warranty Start Date],NOW(),YEAR)
Now that the equation is ready, changes to the query can be made within Power BI.
On the right-hand side of the canvas, select Query1, right-click on it and select New column.
It might not be obvious, but a new equation area opens up (see the purple arrow). I’m not sure what the, “official,” term for it is, but for now I will call it the, “new equation area.” I’m sure someone will tell (correct) me! 😉
Notice that the default name of the Column is Column and because I want the column to be called, “Age,” I’ll change the column’s name too. The final text will look as follows:
Age = DATEDIFF(Query1[Warranty Start Date],NOW(),YEAR)
Notice that the column name changed within Query1!
Adding a Calculated Column to the Table
This is the fun part! Select the table and then select the Age column within Query1.
In real-time the Age column is added to the table, but a Total row is also added. Now take a look at the Age value! There is no way that a computer purchased in 2014 is 12 years old in 2017! What is happening? The answer is that Power BI is trying to be helpful. How can you undo this, “helpfulness?”
Select Age within the Values area.
Notice that by default Power BI will Sum this column for you. Simply change the option to Don’t Summarize and you’re done!
Again in real-time, the table updates and the age values make sense. Okay, I hear what you’re saying, so ignore the age values of my Virtual Machines (VMs)! Instead, look only at the age of the first four physical computers.
You can use the calculated column feature in a number of ways, as I mentioned at the beginning of this post. For example, you could use it to calculate the percentage of free C:\ disk space OR the number of days since Word.exe was last used. The possibilities are endless!
This completes the next step in our mission to create the ultimate Power BI dashboard using SCCM SQL Server data as your data source.
I promise that in the next blog post I’ll show you how to add a chart to your dashboard. I’ll talk about how to apply colors to your dashboard in the post after that one. As I often say, “The foundation needs to be built first before adding all of the extras!”
If you have questions, please feel free to contact me @GarthMJ.