Power Bi

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.?

Series Overview?
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.?

DAX?Elements?

Before getting into the details and subtleties of DAX, it is critical to understand the main DAX?elements:?

Tables?

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?

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?

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.?

Constants?

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.??

Variables?

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.?

Functions?

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.??

Advanced DAX?

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:?

Calculated Tables?

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.?

Target Table?

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.??

Date Calculations?

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.?

Naming convention?

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?

DAX styling?

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.??