Whilst Tableau is the focus of training at the Data School, we also learn Power BI. A couple of my colleagues have also written blogs on using Power BI. I recommend this blog on switching measures and titles dynamically. In this blog post, I explore calculations and parameters in Power BI. Importantly, there are differences in how these are handled in Power BI Desktop versus Power Query Editor.
Functions and Calculations in Power BI
When creating new columns in Power BI report view, the syntax is Data Analysis Expressions (DAX). I have attached a link to a reference list of DAX functions and operators here. I also show below an example of an IF statement:
Conversely, in the Power Query Editor, the Power Query M formula language is used. This link describes details of the language. As shown below, I have created a Custom Column (under the Add Column tab) to clean up a field. I also made a formula to selectively label certain field values as a new “Unknown” group.
Custom Column (“Lifeboat Clean”) showing what the formula above has done
In both cases, an IF statement is utilised to display true/false values. But, the syntax is slightly different.
Parameters in Power BI
I discovered that there are actually 2 types of parameters in Power BI. In the report view, new parameters are created as What-if parameters. I accessed this by clicking the Modelling tab and selecting “New Parameter“. Next, a new window will open which allows configuration of the What-if parameter. Subsequently, a new single column table is generated. I can then reference the value of this parameter in other calculated measures. Previously, I created a parameter where a user can select a desired number to be used to calculate survival rates on the Titanic Disaster. A snapshot of this report is here.
Creating What-if Parameters
In contrast, parameters in Power Query Editor are different. To start, I create new parameters on the home tab under Manage Parameters. From my brief exploration, these parameters are only usable inside the Editor. After some trial and error, I managed to use these in Report view. However, the process is less straightforward than in Tableau. To do this, I changed the parameters by clicking “Edit Queries” and selecting “Edit Parameter“. Next, a message would appear prompting you to apply changes to the query. I have included below an example of this process:
Learning Power BI has been a struggle, given my familiarity with Tableau. I also find the process of having 2 separate ways to do calculations and parameters strange. However, I intend to improve my skills in Power BI to enhance my versatility when visualising data. As shown in this blog post, I have summarised key differences when using Calculations and Parameters in Power BI (compared to Tableau). I hope this has helped you in understanding Power BI.
Featured Image by Fotis Fotopoulos on Unsplash
