Today we changed things up for Dashboard Week, and instead used PowerBI to visualise data about the Titanic. Although we’ve had a couple of sessions now, it still took a while to adjust to the interface and perform the functions I wanted. I ended up spending a lot of time trying to understand how calculations and data modelling work. As a result, my final dashboards don’t explore the visualisation capacities of PowerBI so much as the data manipulation aspect…
On top of getting my head around where to configure fields and formatting, I found two main challenging areas. I will briefly explore these and the dashboards I came up with in relation to them.
Challenge 1. Pivoting data and using DAX calculations
The first challenge was understanding the query and data manipulation terminology within PowerBI. (For instance, the difference between ‘transform’, ‘pivot’ and ‘unpivot’). After a bit of trial and error however, it was relatively straightforward to clean and transform the data..
A key roadblock during the day was understanding the syntax of DAX. (For those who aren’t aware, DAX is a system of functions and operators used in Microsoft to build expressions). The main challenge was understanding how the aggregation for each function works, whether it is applied at row level or not. At times it felt like we were learning to do calculations all the way from scratch again. I made good use of Microsoft’s DAX reference which gives some good starting explanations and examples for each function. Definitely much more to be learnt in this area!
So after unpivoting the data and cleaning up some calculations, I created a simple view exploring the rate of deaths for different nationalities. Here it is:
Challenge 2. Parameterised capabilities
As an extra challenge, we were interested in exploring the parameter capabilities of PowerBI, particularly what it can achieve compared to Tableau. Compared to parameter actions in Tableau, the possibilities in PowerBI are still rather limited and less intuitive to achieve.
To start off, I wanted a parameter that would change the dimension in the chart axis. For example, I wanted to be able to switch between the survival rate per age and per sex. After reading a couple of blogs, I attempted two different approaches to achieve this result. Note that neither actually use the parameter function in PowerBI, but rather require manipulation of the data structure and some calculations.
The first approach I came across required creating a couple of extra tables. I needed one table that contains a list of dimension values to parameterise by. These values can then be used in a slicer as a ‘parameter control’. However, to then join this dimensions table to the main data table, I had to manually create a join table to tell PowerBI which fields to connect. Finally a calculation was needed to tell PowerBI which dimension values to display.
A simpler approach was to pivot the data so you essentially get a column for measure names and measure values. These measure names can then be used in a slicer to essentially filter the view.
Here is the result of both approaches:
Image source: photoangel (www.freepik.com)
