Since its launch in 2011, Microsoft Power BI has sky-rocketed to the forefront of Gartner’s Magic Quadrant for Analytics and Business Intelligence platforms.
A decade later, the platform is firmly established as a proud member of the Microsoft stack. Widely used by a large variety of businesses and industries, Power BI empowers you to visualise your data and share insights across your organisation.
Its two main components are a suite of cloud-based services (“Power BI Service”) and a desktop interface (“Power BI Desktop”). Reports created on desktop and online can also be viewed on the Power BI Android and iOS mobile apps.
Power BI presents several important advantages over other traditional BI tools:
- Performance: Power BI can deal seamlessly with very large datasets, outperforming most tools on the market
- Integration: Power BI integrates extremely well with other applications and Azure services
- Flexibility: It provides analysts with the ability to configure a report-level data model. This means that data could be drawn from different sources and joined in Power BI – granting developers with great analytical freedom
- Custom Visuals: Power BI has a very rich custom visual marketplace, directly embedded into its Desktop interface. This means that users can use, develop and share custom visuals
- Security: Power BI’s row-level security infrastructure allows developers to share reports securely, making sure that users only see the data they are allowed to see
- Active Community: 8 years after its creation, the Power BI community is as active as ever, providing analysts with many resources to find answers to their questions
Working with Power BI
Microsoft developers have made it very easy to design Power BI reports in minutes. However, it takes more than a few clicks to build professional data visualisation solutions.
The flexibility provided by Power BI also comes with a steeper learning curve. In contrast with other BI tools, a good report is much more than pretty visuals. A good Power BI report must have an efficient data model, well optimised and commented DAX expressions, a solid security infrastructure…
This series will go off the beaten paths of basic training, outdated documentation and cryptic Power BI community posts. Through the different articles of this collection, our Power BI experts will share their experience, exploring useful considerations and strategies when coming across the many pitfalls of report development.
The following sections will provide an overview of the main themes covered in this series.
1. Building an efficient Data Model
“Behind every good Power BI report, there is a good data model.”
As a quick reminder, in the Power BI world, a data model could be defined as the organisation and relationships of the different data tables used by the Power BI reports.
A data model is usually represented by these diagrams:
Each box representing a data table and each arrow a relationship (or “join”).
Power BI allows business analysts to create their own data model in the back end of each report. This provides great analytical opportunities. However, this power comes with great responsibilities. The chosen architecture of the data model will heavily influence the performance (i.e. speed) and visualisation opportunities of a given report.
A good data model architecture should address the following considerations:
i. Data Shape: The shape of the incoming data (long vs wide tables, see the diagram below) will impact the horizon of possible data visualisations.
Wide tables are usually easier to work with, as each type of indicator is included in a separate column. This makes it very intuitive to create visuals by dragging and dropping fields onto the canvas. Long tables require slightly more sophisticated Power BI calculations but open many more visualisation possibilities. If you have no control over the shape of the incoming data, tables can be made long or wide within Power BI using the Query Editor.
ii. Data Processing: One of the most important decisions to make when building a report is to determine the optimal data-processing split between Power BI and the data source. Pushing all the data work to Power BI could make reports very slow. An interesting solution is to create a SQL view to take care of most static row-level calculations, leaving the aggregations to Power BI.
iii. Global Filters: Another data model best practice is to create global filter tables joined to the main data table. This way, filters can be synchronised and centrally managed.
2. Row-Level Security (RLS) in Power BI
One of the main strengths of Power BI is its row-level security infrastructure. Row-level security is the practice of filtering content (i.e. data rows) based on the permissions of the user interacting with the reporting solution. In other words, with proper row-level security, users only see the data they are entitled to view.
Row-level security in Power BI is a two-step process.
i. Creating user roles: In Power BI Desktop, developers can create user roles using DAX filter expressions. This can be done in the “Modeling” tab, under the “Manage Roles” functionality.
ii. Assigning users to user roles: Power BI Service administrators can then assign individual users to the roles created in desktop. This is done in the “Dataset” section of a Power BI Service workspace, under the “Security” tab of a given dataset.
The guiding principle of security design is to make the process of adding new roles as easy as possible. The recommended way to do so is to use “dynamic” row-level security. Dynamic Row-Level Security makes user roles responsive to a user’s email address or username, using the USERNAME and USEREMAIL DAX functions.
3. DAX Expressions
DAX expressions and calculations are the backbone of Power BI reporting. Developing enterprise Power BI solutions requires a solid understanding of core DAX concepts.
Data Analysis Expressions (“DAX”) is the native formula and query language of Power BI Desktop and several other tools of the Microsoft BI stack. It uses some Excel functions, complemented by new functions developed to leverage relational data structures and dynamic aggregations.
Before getting into the details and subtleties of DAX, it is critical to understand the main DAX elements:
Tables are referred to by their name, enclosed within quotation marks if the table name contains spaces or special characters (e.g. TableName or ‘Table Name’). These tables could be data tables from the report’s data model or tables calculated using functions such as SUMMARIZE or VALUES.
Several DAX functions take tables as arguments. These functions are usually suffixed by an “X”. As an example, MINX returns the minimum value of a given expression evaluated at each row of a table.
Measures are referred to by their name in brackets (e.g. [MeasureName]). Measures are best thought of as dynamic aggregations. Their value will ultimately depend on the grain at which they are computed. They are table-independent and provide developers with a lot of analytical possibilities.
Columns are generally referred to with the following syntax ‘TableName’[ColumnName]. These columns can either be taken from the original data source or created in Power BI with a DAX expression. Columns are best thought of as static row-level attributes.
DAX expressions can also contain constant values of any of the seven data types supported by DAX (Integer, Real, Currency, Date, Boolean, String and Variant). These constants could be either input by the user or returned by a function.
DAX variables are local variables defined and used within a single expression. The purpose of these variables is to store a given value, or the output of a function. A wise use of variables makes expressions more efficient and readable.
DAX functions process and transform the elements described above. They usually take an object as argument and return another as output. For example, the MIN() function takes a column as argument and returns a constant value, the minimum of that column.
DAX presents endless analytical possibilities. The following points will provide an overview of the art of the possible:
Parameters and Measure Swaps
Measure swaps allow users to select the measure plotted on a chart using buttons or a dropdown menu. Leveraging Power BI parameter and custom parameter tables, dynamic measures can be developed to show the indicator selected by the user:
Power BI is one of the only data visualisation tools allowing users to create calculated tables using DAX expressions and join them back to the original data model. These calculated tables can be leveraged for DAX optimisation, filtering and readability.
Tables can also be calculated within measures using functions such as SUMMARIZE. This function is very useful to evaluate different measures at the desired level of granularity.
“Level of Detail” Calculation (CALCULATE and SUMMARIZE)
The term “Level of Detail” (also known as “LOD”) was made famous by Tableau. The main idea of these calculations is to evaluate an expression in a fixed context. The best way to understand an LOD is through an example. Let’s say that a business analyst wants to analyse the sales contribution of each of the region the company operates in.
By dragging the Region and Sales columns onto the table visual, the table displays the sum of sales for each region. However, to view the sales contribution of each region, the total sum of sales needs to be computed. To do so, a new measure can be created, evaluating the sum of sales fixed to the entire table:
Total Sales = CALCULATE(SUM(DataTable[Sales]), ALL(DataTable))
This can be done with a CALCULATE function. This function evaluates an expression (here the sum of sales) in a given context, following given filter conditions. Here, the context of the CALCULATE is defined by the ALL function – this way, regardless of the row and filter context, the result of this expression will always be the sum of sales for the entire table (i.e. for “all” of the table).
The many subtleties of LOD calculations will be studied in a further article.
DAX date calculations could prove to be tricky. The goal of date calculations is to answer questions such as:
- What were sales on the same day last year?
- What were sales in the last quarter?
- What was the sum of sales last week?
- What was the sum of sales last year?
These calculations are usually based on date additions and subtractions, relying on several key functions, including:
- DATEADD: Adds the desired number of intervals to a date
- DATE: Generates a date from integers (year, month, day…)
- DATEDIFF: Computes the difference between two dates in a desired interval
- CALENDAR: Returns a column containing a contiguous set of dates between a start and end date
Date calculations will be studied more thoroughly in a further article.
4. Power BI style guide
Power BI is not all about DAX and fancy calculations. Reports become much easier to use and audit by following several simple style guidelines.
- Consistency: Using a consistent naming convention is key to make the Fields Pane understandable
- Meaningfulness: Reports containing measures and columns with meaningful names are always easier to navigate
- Ordering: The Fields Pane is ordered in the ascending alphabetical order. This feature can be leveraged to group measures either by theme or function
- Consistency: There are many ways to write DAX expressions. Regardless of the selected style, reports should be consistent with themselves. They should, for instance, have a consistent handling of spacing and capitalisation
- Indentation: Indentations make complex measures readable, highlighting the steps and levels of each function call
- Comments: Just like indentations, comments allow users to quickly understand the purpose and process of a given expression
5. Power BI Service
Most of the above focuses on Power BI Desktop development. However, no enterprise reporting solution can be successful without a solid understanding of Power BI Service. Power BI Service is the “server” of the Power BI ecosystem. It is a space in which datasets, reports and apps can be developed and shared.
The Power BI Service landscape
a. Workspace: a container for dashboards, reports and datasets. In this space, business analysts can develop and share work. Reports developed on Power BI Desktop are uploaded to a specific workspace.
b. Dataset: a collection of data used to create visualisations and reports. It can be built using data from several different sources (SQL database, CSV or Excel File…)
c. Report: analysis of a given dataset developed either on Power BI Desktop or Service. A report usually includes several pages, each page featuring one or more visuals.
d. App: A packaged bundle of one or more report(s) and dataset(s). The goal of the app is to combine several reports into a single reporting solution.
App or Report
When developing Power BI reporting solutions, the first question to ask is: should the visuals be shared on a single report? Or should they be shared in smaller individual reports packaged together in an app?
Both a single report and apps have their advantages:
- Several reports are usually easier to edit and maintain than a single massive report
- Apps provide an additional layer of validation between consumers and developers, as modifications in the underlying reports are not automatically pushed to the app
- The tab navigation provided by apps is quite intuitive
- No interactions are possible between the different reports of an app
- Filters cannot be synchronised across an entire App
- Security design is much simpler with a single report
The right sharing architecture will ultimately depend on the characteristics of the reporting solution, including the number of users, the number of datasets studied, the need for filtering and interactions, the depth of the analysis…
Power BI is a fantastic data visualisation tool, providing business analysts with great analytical possibilities and a solid server infrastructure to share their work.
As a proud Microsoft Gold Partner, across 4 disciplines, the TrueCue team are always on-hand to provide more advice on developing enterprise Power BI solutions.
Contact us today, and we’d be happy to help!
Otherwise, check out the rest of our ‘How-to use Power BI like a Pro’ blog series:
- How-to use Power BI like a Pro: An Introduction
- How-to build an efficient data model in Power BI
- What is the difference between Power BI Calculated Columns and Measures?
- How-to use row level security in Power BI
- Level of Detail Calculations in Power BI
- Power BI Style Guidelines
- Deploying reporting solutions with Power BI Service
- How-to build measure swaps in Power BI
Eliott's life changed when he discovered that machines could learn. From this day onwards, he has been applying his background in Economics and Maths to solve tough business problems and understand the world through data. He is proficient in Python, SQL, Power BI, Tableau and Alteryx, with a passion for discovering new technologies. In his free time, he enjoys playing the guitar, swimming, chess, and night walks.