Wanted to kick start this informatics blog with one of my favorite functions in Microsoft Power BI. Power BI is a data visualization software that is FREE to download to your Windows computer.
I’ll need to write a separate introductory post for the different types of data visualization software out there. Trust me, there are many… think Tableau, Qlik, etc. They all have their advantages and disadvantages, but for now let’s focus on Power BI and specifically the CALCULATE function.
Power BI utilizes the DAX (Data Analysis eXpressions) language to create calculated measures and columns in the data model. There are pros and cons when deciding to create a measure vs a column when developing your visualizations, but that’s for another post (wow… I have a lot more posts to write). The calculate function, however, is arguably the single most important DAX formula to learn if you could only use one.
The CALCULATE function works like the Excel SUMIF() or SUMIFS(), except you can use it as an “anything IF” statement. Let’s look at the basic syntax and an example below:
CALCULATE(<measure expression>, <filter1>, <filter2>, …)
[Cost Rx] = CALCULATE ( [Total Cost] , Invoice[RX Type] <> "OTC" ) // End CALCULATE
In this example, we are taking a calculated measure [Total Cost], which would probably look something like…
[Total Cost] = SUM( Invoice[Cost] ) // End SUM
Then adding a CALCULATE function to “filter” out any rows in the invoice table that have a [RX_Type] of OTC. This example would be relevant if you had a data model with invoice purchases of legend and OTC medications and if your customer wanted the total cost of just the legend medications that you had purchased over a given time. Take a look at the example invoice table below and the following [Total Cost] and [Cost Rx] measures:
This is a very basic example of how the CALCULATE function can work. You can add multiple filter statements to the CALCULATE statement to create very powerful calculated measures within your data model.
The benefits of the CALCULATE statements are that it can be applied to virtually any DAX function and the syntax is very easy to understand and troubleshoot. Try it out for yourself!
I am also posting the excel table and Power BI file (pbix) below if you want to try out the functions for yourself.
Click the github link below for the Power BI Example File: https://github.com/bthomasmoore/Power_BI_Examples.git
I hope this was helpful and gave you a taste of what DAX and Power BI can do. Please feel free to email or post any questions or comments.