For the past 13 weeks on the blog, I have delivered a series of posts concerning Microsoft Exam 70-778, specifically focused towards providing a set of detailed revision notes that cover the broad array of Power BI features assessed as part of the exam. To round things off, today’s blog will bridge together everything I have discussed thus far in the series; with the hope being that this post can be a single reference point for those who have not been following the series to date.

Microsoft Exam 70-778 Overview

The exam, with its full title Analyzing and Visualizing Data with Microsoft Power BI, is targeted towards Business Intelligence (BI) and data professionals who are looking to validate their skills in working with Power BI. The exam is a necessary component, alongside Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel, in attaining the Microsoft Certified Solutions Associate (MCSA) certification in BI Reporting. Successful candidates can then (optionally) pass an additional “elective” exam to gain the Microsoft Certified Solutions Expert (MCSE) certification in Data Management and Analytics.

Skills Measured in the Exam

The skills measured are outlined below, alongside links to the relevant posts from the series and the list of essential points to remember:

Consuming and Transforming Data By Using Power BI Desktop

Connect to data sources.
Skills Measured

May include: Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS)

Revision Notes

Exam 70-778 Revision Notes: Importing from Data Sources

Key Takeaways
  • Power BI supports a broad range of database systems, flat file, folder, application and custom data sources. While it is impossible to memorise each data source, you should at least broadly familiarise yourself with the different types at our disposal.
  • A crucial decision for many data sources relates to the choice of either Importing a data source in its entirety or in taking advantage of DirectQuery functionality instead (if available). Both routes have their own defined set of benefits and disadvantages. DirectQuery is worth consideration if there is a need to keep data regularly refreshed and you have no requirement to work with multiple data sources as part of your solution.
  • Live Connection is a specific data connectivity option available for SQL Server Analysis Services. It behaves similarly to DirectQuery.
  • It is possible to import an existing Excel BI solution into Power BI with minimal effort, alongside the ability to import standard worksheet data in the same manner as other flat file types.
Perform transformations
Skills Measured

May include: Design and implement basic and advanced transformations; apply business rules; change data format to support visualization

Revision Notes

Exam 70-778 Revision Notes: Performing Data Transformations

Key Takeaways
  • The Power Query M formula language is used to perform transformations to data once loaded into Power BI. Although it is possible to do this via code, Power BI allows us to define all of our required data changes from within the interface, without the need to write a single line of code.
  • Each data source connected to represents itself as a Query within Power BI. There are many options at your disposal when working with Queries, such as renaming, merging, duplication and the ability to disable or reference as part of other Queries.
  • There are wide-range of column transformations that can be applied, which are too numerous to mention. The Transform tab provides the best means of seeing what is available, with options ranging from formatting through to grouping and pivoting/unpivoting.
  • New columns are addable via the Add Column tab. You can choose to base new columns on calculations, conditional logic, other column values or as a defined list of ascending numbers, which may be useful for indexing purposes.
  • It is possible to merge or append queries together to suit your specific requirements. Merging involves the horizontal combination of Queries, whereas appending represents a vertical combination.
  • Parameters can be used to help optimise any complex filtering requirements.
  • Where possible, Power Query will attempt to use the most optimal query for your data source, based on the transformation steps you define. This action is known as Query Folding and, in most cases, SQL-derived data sources will support this option by default.
Cleanse data
Skills Measured

May include: Manage incomplete data; meet data quality requirements

Revision Notes

Exam 70-778 Revision Notes: Cleansing Data

Key Takeaways
  • Data can be filtered directly within Power Query, using Excel-like functionality to assist you in only returning the most relevant data in your queries. The data type of each field plays a particularly important part of this, as only specific filter options will be at your disposal if, for example, you are working with numeric data.
  • From a data quality perspective, you typically will need to handle column values that contain one of two possible value types:
    • Errors: This will usually occur as a result of a calculated column field not working correctly. The best solution will always be to address any issues with your calculated column, such as by using a conditional statement to return a default value.
    • Blanks/NULLs: A common symptom when working with SQL derived data sources, your real problems with blank values start to appear when you attempt to implement DAX custom columns/Measures outside of the Power Query Editor. It is, therefore, recommended that these are dealt with via a Replace action, depending on your fields data types. For example, a number field with blank/NULL values should be replaced with 0.
  • The Remove Rows option(s) can act as a quick way of getting rid of any Error or Blank/NULL rows and can also be utilised further to remove duplicates or a range of rows. In most cases, you will have similar options available to you with Keep Rows instead.
  • There are a variety of formatting options available to us when working with text/string data types. These range from fixing capitalisation issues in data, through to removing whitespace/non-printable character sets and even the ability to prepend/append a new value.

Modeling and Visualizing Data

Create and optimize data models.
Skills Measured

May include: Manage relationships; optimize models for reporting; manually type in data; use Power Query

Revision Notes

Exam 70-778 Revision Notes: Create and Optimise Data Models

Key Takeaways
  • Relationships form the cornerstone of ensuring the long-term viability and scalability of a large data model. Assuming you are working with well-built out, existing data sources, Power BI will automatically detect and create Relationships for you. In situations where more granular control is required, these Relationships can be specified manually if needed. It is worth keeping in mind the following important features of Relationships:
    • They support one-to-one (1:N), one-to-many (1:N) and many-to-one (N:1) cardinality, with many-to-many (N:N) currently in preview.
    • Filter directions can be specified either one way or bi-directionally.
    • Only one relationship can be active on a table at any given time.
  • It is possible to sort columns using more highly tailored custom logic via the Sort By Column feature. The most common requirement for this generally involves the sorting of Month Names in date order but can be extended to cover other scenarios if required. To implement, you should ensure that your data has a numbered column to indicate the preferred sort order.
  • Moving outside of the Power Query Editor presents us with more flexibility when it comes to formatting data to suit particular styling or locale requirements. While the majority of this functionality provides date/time and currency formatting options, for the most part, it is also possible to categorise data based on Location, the type of URL it is or on whether or not it represents a Barcode value; these options can assist Power BI when rendering certain types of visualizations.
  • There may be ad-hoc requirements to add manually defined data into Power BI – for example, a list of values that need linking to a Slicer control. The Enter Data button is the “no-code” route to achieving this and supports the ability to copy & paste data from external sources. For more advanced scenarios, you also have at your disposal a range of M code functionality to create Lists, Records and Tables, which can be extended further as required.
Create calculated columns, calculated tables, and measures
Skills Measured

May include: Create DAX formulas for calculated columns, calculated tables, and measures; Use What If parameters

Revision Notes

Exam 70-778 Revision Notes: Using DAX for Calculated Columns

Key Takeaways
  • DAX is the primary formula language when working with datasets outside of Power Query. It includes, to date, more than 200 different types of functions that can assist in all sorts of data modelling.
  • An important concept to grasp within DAX is context and, specifically, row context (formulas that calculate a result for each row in a dataset) and filter context (formulas that automatically apply any filtering carried out at report level).
  • The sheer amount of DAX functions available makes it impossible to master and remember all of them, particularly when it comes to the exam. Your learning should, therefore, focus on learning the general syntax of DAX and the general types of functions available (aggregation, date/time etc.)
  • There are three principal means of utilising DAX with Power BI:
    • As Measures: These typically present a scalar value of some description, often an aggregation or a result of a complex formula. Using them in association with a Card visualization type is recommended, but this is not a strict requirement.
    • As Calculated Columns: Similar to the options available within Power Query, Calculated Columns provide a dynamic and versatile means of adding new columns onto your datasets. Compared with the options available within Power Query and the complexity of the M language, DAX Calculated Columns might represent a more straightforward means of adding custom columns onto your datasets.
    • As Calculated Tables: A powerful feature, mainly when used in conjunction with Calculated Columns, you have the ability here to create entirely new datasets within the model. These will typically derive from any existing datasets you have brought in from Power Query, but you also have functionality here to create Date tables, sequence numbers and manually defined datasets as well.
  • What-if Parameters provide of means of testing DAX formulas, as well as allowing report users to perform predictive adjustments that can affect multiple visualizations on a report.
Measure performance by using KPIs, gauges and cards.
Skills Measured

May include: calculate the actual; calculate the target; calculate actual to target; configure values for gauges; use the format settings to manually set values

Revision Notes

Exam 70-778 Revision Notes: Utilising KPIs with Gauge Visualisations

Key Takeaways
  • There are two principle visualization types available within Power BI to help track actual-to-target progress – KPIs and Gauges.
  • KPIs provide a more visually unique means of a binary success/fail determination when tracking towards a target. It is also possible to use KPI’s to track variance over time via the Trend axis. The Indicator will typically be the result of some form of aggregation or Measure.
  • Gauges provide a less visually distinctive, but non-binary, mechanism of viewing progress towards a target. Gauges support more potential field well values when compared with KPIs, nearly all of which are optional in some way. You can also manually define some of these values, for situations where your data model does not contain the required information.
  • All visualizations within Power BI are modifiable from a display or formatting perspective. The same basic options will generally be supported – such as changing a font type or background colour – with more specific configuration properties available per unique visualization type. For example, a KPI visualization can be customised to hide the background Trend Axis entirely. All of these options are designed to give developers greater control over the look and feel of their reports and to mirror them as closely as possible to any potential branding requirement.
  • When building out a solution designed to monitor progress within Power BI, the steps involved will typically be more in-depth than merely creating a new visualization. In most cases, there will be a requirement to bring together a lot of the other skills that have been discussed previously within this series – such as creating DAX formulas, modifying data within Power Query or bringing together different data sources into a single model. It is essential, therefore, not to underestimate the amount of time and effort involved in creating a practical solution that takes advantage of KPIs or Gauges.
Create hierarchies
Skills Measured

May include: Create date hierarchies; create hierarchies based on business needs; add columns to tables to support desired hierarchy

Revision Notes

Exam 70-778 Revision Notes: Creating Hierarchies

Key Takeaways
  • Hierarchies within Power BI provide a means of logically categorising data into an order of preference or precedence, providing greater flexibility to Power BI report users when they interact with visualizations.
  • Date Hierarchies are created and managed automatically by Power BI for each Date or Date/Time field defined within your model. These automatically create fields that contain the Year, Quarter, Month & Day values from the respective date fields. These fields can then be utilised as part of a Table visualization or within a DAX formula. Date Hierarchies can also be completely disabled if required.
  • Custom (or User-Defined) Hierarchies need to be created manually and provide additional customisation options when compared to Date Hierarchies, such as the number of fields they contain, the order and its name. A Custom Hierarchy will typically make use of one of several Parent/Child DAX functions, such as PATH or PATHITEM.
  • Including a hierarchy as part of a chart visualization, such as a Pie chart or Donut chart, opens up other drill-down capabilities around your data. Indicated by the additional arrow icons included at the top of the visualization, they provide the means for users to interrogate data points that interest them the most straightforwardly.
Create and format interactive visualizations.
Skills Measured

May include: Select a visualization type; configure page layout and formatting; configure interactions between visual; configure duplicate pages; handle categories that have no data; configure default summarization and data category of columns; position, align, and sort visuals; enable and integrate R visuals; format measures; Use bookmarks and themes for reports

Revision Notes

Exam 70-778 Revision Notes: Create and Format Interactive Visualizations

Key Takeaways
  • Power BI delivers, out of the box, a range of different visualizations that cater towards most (if not all) reporting requirements. Should you find yourself in need of additional visualizations, then Microsoft AppSource is your go-to destination for finding visualizations developed by others. If you have experience working with either Node.js or R, then these can be used to build bespoke visualizations also.
  • When first developing a report, you should be able to match a requirement for a specific visualization type, to ensure that you are delivering a solution that is both meaningful and useful. From an exam perspective, this becomes a more critical consideration, and you should be prepared to suggest the most optimal visualization to use when given a specific scenario.
  • After adding visualization’s to your report, you have additional options available to customise them further. For example, you can specify a preferred sorting order for your data, override any summarizations used and move/align your visual on the report page.
  • By default, visualizations in Power BI are designed to change automatically, based on how users interact with the report. All of these options are controllable via the Edit interactions button, allowing you to specify your preferred cross-filtering and cross-highlighting conditions.
  • There is a range of report page customisation options available to developers. It is possible to resize a page to any possible height/width, allowing you to optimise your report for specific devices. Also, you can modify the colour of a page (or its wallpaper) or add an image instead. Pages can also be renamed, reordered or duplicated.
  • Measures can be formatted in the same way as calculated columns, meaning you can specify a data type or, for numerics, modify the number of decimal places.
  • Bookmarks allow developers to set up “checkpoints” within a report, based on how a report page has been filtered. These can then be used to automatically navigate the user through a report, applying these filtering steps automatically. This feature can help transform your report into an interactive story.
  • Visualizations will automatically inherit their various colour properties from the currently selected report theme. Although these can be modified granularly, the fastest and most consistent way of making these changes en masse is to change the Theme. Power BI includes some Themes out of the box, but you also have the option of building your own using a custom JSON file; this can then be imported into different reports, providing a portable means of enforcing a particular branding requirement.
Manage custom reporting solutions
Skills Measured
  • May include: Configure and access Microsoft Power BI Embedded; enable developers to create and edit reports through custom applications; enable developers to embed reports in applications; use the Power BI API to push data into a Power BI dataset; enable developers to create custom visuals
Revision Notes

Exam 70-778 Revision Notes: Managing Custom Reporting Solutions

Key Takeaways
  • Power BI Embedded is an Azure hosted offering that allows you add Power BI Report content into bespoke applications. This deployment option can be incredibly useful if you wish to make available your Power BI solution to users outside of your organisation or if you have an existing, bespoke application system that can benefit from utilising Power BI content. An Azure subscription is required to begin working with Power BI Embedded and you are billed based on node size, not individual user licenses. All Power BI content requires publishing to the online service before its contents become available for Power BI Embedded to access. Report developers will, therefore, need granting a Power BI Professional license to carry out these activities.
  • The Power BI API grants access to developers to perform automation or administrative actions programmatically against the Power BI Online service. Utilising a REST API, developers can determine the optimal programming language of choice to interact with the API, allowing them to streamline the deployment of Reports or Dashboards to the Power BI service or leverage additional functionality when utilising Power BI Embedded. The API can also cater to specific data load requirements, although more complex needs in this area would require addressing via alternate means (SSIS, Azure Data Factory etc.)
  • Developers can add their own bespoke visualizations to a Power BI Report by either developing them using Node.js or using the R language. The first of these options facilitate a more streamlined deployment mechanism and allows developers to add their visualizations to AppSource, whereas the second option may be more useful for complex visualization types with an analytical or statistical function.

Configure Dashboards, Reports and Apps in the Power BI Service

Access on-premises data
Skills Measured

May include: Connect to a data source by using a data gateway;publish reports to the Power BI service from Power BI Desktop; edit Power BI Service reports by using Power BI desktop

Revision Notes

Exam 70-778 Revision Notes: Report Publishing, On-Premise Gateway & Creating Dashboards

Key Takeaways
  • The Power BI On-Premise Gateway provides a streamlined route to working with non-cloud data sources within Power BI, Microsoft Flow and PowerApps. As a lightweight and easy-to-configure client application, it supports a wide variety of data sources, making them accessible as if they were in the cloud. Once set up, corresponding Data Sources are then made available for configuration and for leveraging as part of any Power BI Dataset.
  • Reports can be published into Power BI Online, meaning that they become accessible online and to a broader group of users, without requiring access to Power BI Desktop. Reports need deploying into a Workspace, which can be created manually or derived from an Office 365 Group. Each Report contains a corresponding Dataset, where all queries defined within Power BI Desktop exist.
  • Reports that already exist on Power BI Online can be updated by just publishing a new version of the Report from Power BI Desktop. It is also possible to modify Reports from directly within the browser and by downloading a copy of the .pbix Report file as well, which can then be altered and re-published.
Configure a dashboard
Skills Measured

May include: Add text and images; filter dashboards; dashboard settings; customize the URL and title; enable natural language queries

Revision Notes

Exam 70-778 Revision Notes: Report Publishing, On-Premise Gateway & Creating Dashboards

Key Takeaways
  • Dashboards provide a means of grouping together various content as tiles, designed for at-a-glance analysis and optimal end-user experience.
  • The list of content that can be pinned to a Dashboard includes:
    • Visualizations
    • Web content
    • Images
    • Text boxes
    • Videos
    • Custom streaming data
  • Pinned content can be re-arranged on Dashboard via drag and drop functionality. It is also possible to resize tiles to any given height/width.
  • Within the settings of a Dashboard, it is possible to enable/disable features such as natural language queries (Q&A’s) and Notes.
  • Some features of a Dashboard are only available if you have a Power BI Professional subscription, such as sharing and email subscriptions.

 

Publish and embed reports
Skills Measured

May include: Publish to web; publish to Microsoft SharePoint; publish reports to a Power BI Report Server

Revision Notes

Exam 70-778 Revision Notes: Publish and Embed Reports

Key Takeaways
  • The Publish to web option allows for non-licensed, external users to view a Power BI Report in its entirety. A URL and IFrame embed code can be generated for this at any time within the portal and then dropped into virtually any website. Although you will lose some functionality when deploying a Report out in this manner, you can expect that users will be able to perform most types of interactions with visualizations, Report pages and other components, as if they were accessing the Report through Power BI Online. In some cases, you may be unable to use the Publish to web option if your Report uses certain kinds of features, such as R Visuals or row-level security. You must also take into account any privacy or data protection concerns, as Reports deployed out in this manner will be publically accessible; where this is an issue, the Embed option is available as a secure alternative.
  • There are three steps involved if you wish to add a Report to SharePoint. First, you must generate the unique SharePoint embed URL within Power BI. Secondly, you then need to add on the dedicated control for this feature on your target SharePoint page and configure the relevant display options. Finally, you then need to ensure that all SharePoint users have been granted access to the Report, either at a Workspace level (recommended option) or by having the Report shared with them. By implication, in this scenario, all SharePoint users would have to have at least a Power BI Professional license to take full advantage of this functionality.
  • Publishing a Report to Power BI Report Server is mostly the same as if you were to do the same with the online version of the product. Instead of selecting a Workspace to add the Report to you, specify the name of the Report Server folder where the Report will reside. From a development standpoint, the dedicated Power BI Desktop for Power BI Report Server must be used and may differ in functionality from the “normal” version of the tool. There is also no option to edit a report from within Power BI Report Server like you can through the online version.
Configure security for dashboards, reports and apps.
Skills Measured

May include: Create a security group by using the Admin Portal; configure access to dashboards and app workspaces; configure the export and sharing setting of the tenant; configure Row-Level Security

Revision Notes

Exam 70-778 Revision Notes: Securing Power BI Dashboards, Reports and Apps

Key Takeaways
  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. Additional Workspaces can be added through Office 365 Groups or by installing a Power BI App from AppSource. Dashboards and Reports created within your a Users Workspace are shareable to other Users, provided that your account has a Power BI Professional license assigned to it.
  • To help manage permissions to Dashboards/Reports in a more efficient manner, Administrators can create Security Groups on the same Office 365 Tenant where Power BI Online resides. These can contain multiple groups of Users, allowing administrators to minimise the amount of effort involved in managing Dashboard/Report access. Most crucially, this will also enable Users that do not have an Exchange Online mailbox to access Dashboards/Reports when they are shared out in this manner.
  • Administrators have a whole host of options available to them within the Tenant settings area of the Admin Portal. These include, but are not limited to:
    • Export and Sharing Settings
    • Enable/Disable Content Sharing
    • Enable/Disable Publish To Web
    • Enable/Disable Export Reports as PowerPoint Presentations
    • Enable/Disable Print Dashboards and Reports
    • Content Pack and App Settings
    • Integration Settings
    • Custom Visuals Settings
    • R Visuals Settings
    • Audit and Usage Settings
    • Dashboard Settings
    • Developer Settings
  • All of these settings can be enabled for a specific security group, the entire organisation (excepting specific security groups) or allowed for particular security groups, excluding all others in the organisation.
  • Row-Level Security (RLS) allows report developers to restrict data, based on Roles. Row-level DAX evaluation formulas are used to achieve this, which filters the data that is returned, depending on a TRUE/FALSE logic test. To utilise the feature, you must define both the Roles and DAX formulas for each query within your data model. Then, after deploying your Report to Power BI Online, you then assign Users or Security Groups to the Role(s) created within Power BI Desktop. It is possible to view the effect of a Role at any time, within Power BI Desktop or Online, via the View As Role functionality. With the wide-array of DAX formulas available, including specific ones that return the details for the current user accessing a Report, it is possible to define very granular filtering within a Power BI report, to suit particular security or access models.
Configure apps and apps workspaces.
Skills Measured

May include: Create and configure an app workspace; publish an app; update a published app; package dashboards and reports as apps

Revision Notes

Exam 70-778 Revision Notes: Working with Apps and App Workspaces

Key Takeaways
  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. It is also possible to create additional Workspaces, either through the Power BI Online interface or by creating an Office 365 Group. A new experience for creating Workspaces is currently in preview which, once released, would negate the need for each Workspace to have an associated Office 365 Group.
  • When creating a Workspace, you can define various settings such as the type of access each user has (read-only or ability to modify its content), its members and whether it requires assignment to a Power BI Premium node. It is not possible to change the access type for a Workspace after creation, but you can freely change its name or modify its membership at any time.
  • The contents of a Workspace can be published as an App, enabling you to expose your solution to a broader audience within or outside your organisation. Once published, users navigate to the Power BI AppSource store for their tenant, which lists all Apps available for installation. Once installed, they will then become visible from within the Apps area of the application. You can update content within an App at any time by republishing its corresponding Workspace. It is also possible to define individual properties within an App, such as its description, access rights and landing page. To install and use Apps, the user in question must have a Power BI Professional license.

Additional Preperation Resources

The official Microsoft exam reference book is a helpful learning aid for this exam, particularly given that it includes numerous exercises that you can work through to familiarise yourself with different Power BI functionality. There is also an online course available on the edX website which, despite not covering the whole exam syllabus, does provide a useful visual aid and includes a lot of the features you are expected to know for the exam. Finally, nothing beats actually working with the product itself and trying out the various features yourself. Power BI Desktop is a free download and, with access to one of the sample databases provided by Microsoft, you can very quickly provision an environment on your own home computer to enable you to experience everything that Power BI has to offer.

Exams are always a nightmarish experience, both when preparing for them and when you are sat there in the test centre. I hope that this post, and this whole series, proves to be useful in helping with your exam preparation and getting you ready to pass the exam with flying colours 🙂

Happy New Year! As 2019 dawns upon us, Microsoft Business Application professionals start in the right place, as the concept of the Power Platform starts to take hold. Through this, it is pleasing to observe more consistency across these range of products, with regular releases, increased integration and better learning tools, provided directly from Microsoft. I’ve mentioned this previously on the blog, but it is worth emphasising again the increased importance Power BI has from a Dynamics CRM/365 Customer Engagement standpoint. With this in mind, having a New Years resolution to learn more about it and to earn a technical qualification in the subject will hold you in good stead in future. If you are reading this now, then hopefully you already have this resolution. 🙂

Today’s post will continue my series focused on providing a revision tool for Microsoft Exam 70-778. This week, we move into the broad subject area Create and format interactive visualizations, which revolves around the following skill areas:

Select a visualization type; configure page layout and formatting; configure interactions between visual; configure duplicate pages; handle categories that have no data; configure default summarization and data category of columns; position, align, and sort visuals; enable and integrate R visuals; format measures; Use bookmarks and themes for reports

Let’s start by providing an overview of just what a visualization is, before deep-diving into the specific topic areas listed above. The examples provided in this post refer to the latest Power BI Desktop sample report 2018SU12 Blog Demo – December.pbix, which can be downloaded from GitHub using this link.

Visualization Overview

The majority of topics covered in this series have all concerned the foundations of a successful Power BI report – the data sources, the data quality enhancement work and the required DAX wizardry to create custom columns, Measures or table objects to supplement any requisite Power Query manipulation. With the necessary foundations, walls and various utilities built for your Power BI “house”, the final and most important topic concerns the decoration – creating impactful and meaningful visualizations that help to display your data most appropriately. The great thing about using Power BI as your Business Intelligence tool is the vast array of default and custom visualizations that are available when developing a report. The Visualizations pane on the Report tab lists all of the possible visuals available for your report, described further in the list that follows (in order, left to right, top to bottom):

  • Stacked bar chart
  • Stacked column chart
  • Clustered bar chart
  • Clustered column chart
  • 100% stacked bar chart
  • 100% stacked column chart
  • Line chart
  • Area chart
  • Stacked area chart
  • Line and stacked column chart
  • Ribbon chart
  • Waterfall chart
  • Scatter chart
  • Pie chart
  • Donut chart
  • Treemap
  • Map
  • Filled map
  • Funnel
  • Gauge
  • Card
  • Multi-row card
  • KPI
  • Slicer
  • Table
  • Matrix
  • R script visual
  • ArcGIS Maps for Power BI

Power BI also supports custom visuals, provided by ISV’s or Node.js developers, that allow you to include additional visualizations in your report. You can work with these by clicking on the ellipsis icon on the Visualizations pane:

There are two web links relating to custom visuals that are worth considering further:

  • The Business Apps marketplace, accessible also via the Import from marketplace button, lets you either add new custom visuals directly into Power BI or download versions that you can then import using the Import from file button. There are a lot of great visuals available here, that can help to supplement your existing reports and take some of the aggravation out when implementing more complex requirements (e.g. displaying Gannt chart visuals).
  • The Developing a Power BI custom visual Microsoft Docs Tutorial walks you through the required steps to build out a custom visual using Node.js.

Just dragging and dropping a visual onto a report and adding a few field values may not be enough to meet a specific business requirement. For this reason, you should consider the following when working with visualizations:

  • Does the visual require sorting in ascending, descending or by a particular column order? If so, then clicking on the ellipses button at the top right of the visualization will expose several sortation options, which may differ based on the underlying dataset:
  • Are there blank categories within your data? If so, you may encounter a similar issue as indicated in the screenshot below, with a (Blank) category value:
  • Resolutions to this problem can vary – for example, you could go back into your query and add a default value for all blank columns values – but a quick way to potentially fix this is to click on the down arrow next to the field and select the Show items with no data option:
  • Does the default summarization need to be overridden for the Values field? We’ve seen previously in this series how it is possible to specify the default summarization for each column in your dataset. On occasions where this needs changing, you can again use the right arrow next to the field to carry this out:

Finally, you also have some additional options available by selecting the Format tab with a visual selected:

The options above the Arrange heading should be self-explanatory, but it is worth focusing on the Edit interactions button. An expected experience with Power BI is, as you begin to filter visualizations, others on the report update accordingly by applying the same filter. This behaviour can be changed using the Edit interactions button, allowing you to specify whether other visuals on the report:

  • Apply a cross-filter
  • Apply a cross-highlight
  • Do nothing

The default action for most visualizations is to apply a cross-filter. The sequence below demonstrates how this can be disabled using the Edit interactions button:

A handy feature to have at your disposal, further details regarding the Edit interactions button can be found on the Change how visuals interact in a Power BI report Microsoft Docs article.

Deciding Which Visualization To Use

Both for a real-life and exam scenario, you should be prepared to identify when a visualization will be appropriate to use, based on a stated list of requirements. As a general rule of thumb, if:

  • You need to compare data between different categories, then use a Bar/Column or Ribbon Chart.
  • The requirement is to compare data values across a date range, then use a Line or Area Chart.
  • You are working with a dataset that contains multiple fields with wide value ranges, then use a Combo Chart.
  • There is a need to show significant variances across a set of data or to highlight significant amounts in comparison to others, then use a Waterfall Chart.
  • You are working with two metrics that have a relationship between them and you need to visualise diverse value types, then use a Scatter Chart.
  • Data needs to be grouped by a distinct category and shown as part of a whole value, then use a Pie or Doughnut Chart.
  • The requirement it to distinctively show proportions of an overall part and, by association, the most significant/smallest contributors, then use a Treemap visualization.
  • Your data is geographically based, and there is a desire to provide drill-down capability, then use a Map, Filled Map or ArcGIS Maps for Power BI visualization.
  • You are working with data based on distinct stages (e.g. Lead data from Dynamics CRM/365 Customer Engagement, grouped by Business Process stage), then use a Funnel visualization.
  • The data needs to be consumed via a single value or viewed as represented in the underlying data, then use a Card, Multi-Row, Table or Matrix visualization.
  • There is a need to provide users with the capability to filter data “on the fly”, then use a Slicer visualization.
  • An R script requires incorporation into your report, use an R visualization (discussed in further detail later on in this post).
  • You need to report data concerning Key Performance Indicator (KPI) monitoring, then a KPI or Gauge visualization should be chosen. These are both discussed in greater detail in my previous post on this subject.

Report Page Options

Visualizations form an essential part of the look and feel of a report, but further options are also available from a report design perspective. With a report page selected and with the Format paintbrush icon chosen (which is accessible in the same way as described in section Visualization Format Settings in my post concerning KPIs), you have access to additional options relating to the currently selected page:

The options available here include:

  • Page Information: Here, you can change the name of the page, as well as enable/disable the Tooltip and Q&A features for this page.
  • Page Size: The options here let you adjust the size of the page to one of several options:
    • 16:9 (1280 x 720)
    • 4:3 (960 x 720)
    • Cortana (296 x 592)
    • Letter (816 x 1056)
    • Custom
  • Page Background: From here, you can change the background colour of the page and adjust its transparency. It is also possible to add a background image here too.
  • Page Wallpaper: Potentially confusing when compared against the Page Background options, the options in this section let you adjust the colour outside of the main page area. The best way of understanding how this looks is to take a look at the following garish example, which shows a page that has had both background and wallpaper colours specified:

Never develop a report that looks like this, by the way. 🙂

When it comes to working with multiple pages, you also have the following options available, accessible through right-clicking a page:

Formatting Measures

Measures, as with other column types (derived either from a query or a DAX formula), can be formatted in numerous different ways. The steps involved here do not differ significantly from the options discussed in the Formatting Columns section of my previous post concerning data model optimisation; select the Measure in question from the Fields pane, navigate to the Modeling tab and the appropriate options will be made available to you.

R Visuals

R provides developers with the means of building highly sophisticated and bespoke visualizations, that will typically be consumed for statistical analysis. These can be added to Power BI Desktop and linked to any data source in your report. There are two necessary components required to start working with R visuals in this manner:

  • You must install R on your local machine. There are many versions available, with R Open 3.5.1 from Microsoft being the logical choice for beginners.
  • Within the Options area of Power BI Desktop, you must verify that the correct Detected R home directories dropdown is selected. If you have installed R Open 3.5.1 on your machine, then this should be filled for you automatically, as follows:

(You may also be prompted to Enable script visuals, as indicated by the dialog box below; ensure that the Enable option is selected)

Beyond the basics of getting started with R in Power BI, which is all that is required from an exam perspective, this is a topic too complex to cover in this blog post.

Bookmarks

The ability to pre-configure a report page, from a filtering standpoint, can be incredibly useful for those who consume a Power BI report. Bookmarks seek to address this need, by allowing developers to build a guided “story” in their report. All that is required is for a report page to be filtered accordingly and then for a Bookmark to be added – couldn’t be simpler! For example, the 2018SU12 Blog Demo – December.pbix sample report contains several Bookmarks, accessed by opening the Bookmarks Pane on the View tab:

With the Selection pane also enabled, you can then easily navigate between all Bookmarks by clicking the View button above and using the arrow icons on the bottom of the page:

Through the Bookmarks pane, it is also possible to re-order Bookmarks and to group them by a category, such as a page. Simple to setup, but powerful when utilised, they are a feature which I think gets overlooked and should be considered if you are building out a report for beginner Power BI users.

Themes

A feature that has typically been available with every Microsoft Business Intelligence (BI) application is extensive design capabilities, atypically to suit any bespoke branding requirements that an organisation may have. Power BI is no different in this regard, because, as well as being able to specify the colour of visualizations individually, developers also can define a top-level Theme that will automatically apply to all report visualizations. The Switch Themes button provides users with the ability to modify their Report theme at any time, located on the Home tab:

As shown in the image above, you can:

  • Change the Theme from Default to one of the other include Theme definitions within Power BI, such as Electric or High Contrast.
  • Add a custom Theme to your report using the Import theme button.
  • Browse the Power BI Community Theme gallery to download a custom Theme developed by someone else.
  • Get additional help on theming through the How to create a theme button, which links to the Use Report Themes in Power BI Desktop support article.

The ability to import and develop bespoke Themes is a topic that requires further discussion. All Themes are defined as JSON files, which outline the different hex colour values that Power BI allocates for each visualization type. There is a hell of a lot of options available here, depending on how masochistic you are feeling stringent your branding requirements need to be. For the exam and most real-life scenarios, a straightforward JSON file (provided courtesy of Microsoft) may resemble the below:

{
    "name": "St Patricks Day",
    "dataColors": ["#568410", "#3A6108", "#70A322", "#915203", "#D79A12", "#bb7711", "#114400", "#aacc66"],
    "background":"#FFFFFF",
    "foreground": "#3A6108",
    "tableAccent": "#568410"
}

Once saved as a file with the name St Patricks Day.json and imported into the sample 2018SU12 Blog Demo – December.pbix report, we get this rather…distinctive look:

The options available with Themes are always worth keeping in the back of your mind and, chances are, they can provide the means towards ensuring consistently branded Power BI reports.

Key Takeaways

  • Power BI delivers, out of the box, a range of different visualizations that cater towards most (if not all) reporting requirements. Should you find yourself in need of additional visualizations, then Microsoft AppSource is your go-to destination for finding visualizations developed by others. If you have experience working with either Node.js or R, then these can be used to build bespoke visualizations also.
  • When first developing a report, you should be able to match a requirement for a specific visualization type, to ensure that you are delivering a solution that is both meaningful and useful. From an exam perspective, this becomes a more critical consideration, and you should be prepared to suggest the most optimal visualization to use when given a specific scenario.
  • After adding visualization’s to your report, you have additional options available to customise them further. For example, you can specify a preferred sorting order for your data, override any summarizations used and move/align your visual on the report page.
  • By default, visualizations in Power BI are designed to change automatically, based on how users interact with the report. All of these options are controllable via the Edit interactions button, allowing you to specify your preferred cross-filtering and cross-highlighting conditions.
  • There is a range of report page customisation options available to developers. It is possible to resize a page to any possible height/width, allowing you to optimise your report for specific devices. Also, you can modify the colour of a page (or its wallpaper) or add an image instead. Pages can also be renamed, reordered or duplicated.
  • Measures can be formatted in the same way as calculated columns, meaning you can specify a data type or, for numerics, modify the number of decimal places.
  • Bookmarks allow developers to set up “checkpoints” within a report, based on how a report page has been filtered. These can then be used to automatically navigate the user through a report, applying these filtering steps automatically. This feature can help transform your report into an interactive story.
  • Visualizations will automatically inherit their various colour properties from the currently selected report theme. Although these can be modified granularly, the fastest and most consistent way of making these changes en masse is to change the Theme. Power BI includes some Themes out of the box, but you also have the option of building your own using a custom JSON file; this can then be imported into different reports, providing a portable means of enforcing a particular branding requirement.

Visualizations are a HUGE topic for the exam, with a lot of detail that requires careful consideration. I hope this post has provided the right balance between highlighting the most critical areas, without going into minute detail. I would, therefore, urge you to go away and carry out studying yourself to gain a greater appreciation of this subject area. Next weeks post will be somewhat lighter reading, as we take a look at how application developers can integrate Power BI within their existing apps.

As we move into the festive period, now is the time to put your feet up, relax, take stock for the year ahead…or, if you are reading this over Christmas, grab the opportunity to learn more about Power BI 🙂 . In the seventh post in my series concerning Microsoft Exam 70-778, we move away from the suitably large subject area of DAX into a topic much more visually focused and accessible – Measure performance by using KPIs, gauges and cards. The related skills for this exam area are:

Calculate the actual; calculate the target; calculate actual to target; configure values for gauges; use the format settings to manually set values

As part of this week’s post, I hope to delve deeper into each of these skill areas, with the aim of providing a revision tool for the exam or a general learning aid for those getting to grips with Power BI for the first time. To follow on as part of any examples that follow, make sure you have downloaded the WideWorldImporters sample database and hooked up Power BI Desktop to the tables described in my first post in this series.

KPI Overview

A standard reporting requirement, and one which typically forms the basis of any contractual performance monitoring is the studying of Key Performance Indicator (KPI) figures. These are designed to provide an executive level view to answer questions such as “How is our sales pipeline progressing?” or “Have we met our objectives to reduce the number of monthly complaints down to X number?“. The whole process of collating together diffuse data sources to present this information may, in the past, have been exhausting. With Power BI, it is possible to deploy a dedicated KPI visualization that allows you to straightforwardly and consistently report headline figures, track variance over time and receive immediate visual cues to flag up any potential issues.

To get going with your first KPI visualization, you must supply two critical pieces of data:

  • Indicator: This is the “actual” value that needs reporting against a target, and will typically be some form of aggregation contained within a Measure.
  • Trend axis: Representing the performance of your indicator based on a range of data, the most typical axis type to use here would be some form of date value – such as month or year.

A KPI configured with just these two properties will work but looks…well…bland and not very useful at all:

This is why a Target Goal should additionally be specified. Adding this will allow for the appropriate colour to be applied to the visualization, to indicate how well the Indicator is performing against your target. The great thing about this is that up to 2 Target Goal values can be specified if needed. You potentially lose some functionality here, as the Distance percentage value will no longer display correctly; but this may be useful if, for example, you have an aspirational target that there would be a benefit in hitting, but is not a strict requirement. When the value of one Target Goal is determined to be fulfilled, but the other isn’t, the Visualization will display Amber by default, as indicated below:

The example at the end of this post will walk through the steps involved in setting up a KPI.

Gauge Visualizations

We got a sneak peek into Gauge’s when working with What-If Parameters in last week’s post, but a more in-depth discussion regarding them is necessary. Gauges provide a different means of viewing how a subset of data is performing against a target. They differ from KPI’s primarily due to the additional configuration options they afford and the potential they have to provide a faster means of interacting with key data points on the visualization. The configurable field well properties for a Gauge is viewable below, in the screenshot and descriptive bullet point provided:

  • Value: This is the value that requires tracking, similar to the Indicator within KPI’s.
  • Minimum value: The minimum amount that needs to be hit by the Value before the Gauge will be filled in.
  • Maximum value: The maximum amount that needs to be hit by the Value before the Gauge stops filling (i.e. a Gauge with a Value of 105 and a Maximum value of 100 would display as full).
  • Target value: The target that needs hitting, which will be presented as a black line on the Gauge, as shown below:
  • Tooltips: These are additional field values that will be displayed to the user when hovering over the Gauge. These fields can either be aggregated variants of columns or a Measure.

Gauges behave differently from KPI’s in that they will “work” with only one field well specified. For example, adding a Measure with a value of 8 million as the Value will display a half-full Gauge, as indicated below:

Notice that, in this scenario, the Gauge assumes the maximum amount to be double of what is specified in the Value field well – in this case, 16 million. A Gauge configured so frugally has little purpose and you should, as a minimum, ensure that the Maximum value and Target value field wells are also set, as demonstrated below:

A nice little feature of Gauges is the ability to specify any of the potential field well values manually, within the Formatting options (discussed in the next section).  The Value field well must be populated already within your Gauge to support this feature. You will then have the ability to manually specify the other values within the Gauge axis options area:

Any manually defined figure will be automatically overwritten and deleted if you choose to use a field from a table instead, so take care here to avoid any data loss.

Gauges address a similar need to KPI’s, in that they allow you to view possible progress against a maximum or target value. A significant downside in using them is that they present a less visual means of showcasing potential issues with your data. You should weigh these advantages/disadvantages up when determining whether to use a Gauge or a KPI as part of your report.

Visualization Format Settings

All Visualization’s within Power BI can be customised, often to the extent where they can look indistinguishable from when you first add them onto your report. The Formatting tab on the right-hand pane is your go-to destination in this regard, and is accessible by clicking the paintbrush icon while selecting any Visualization:

The types of things that are achievable via these options include, but are not limited to:

  • Modifying all aspects of the visualizations Title, such as its visibility, the text displayed, font colour/size/type and alignment.
  • Adding a colour background to the visualization.
  • Including a description for the visualization.
  • Toggling a border for the visualization and its colour.
  • Fine-tuning the exact location of the visualization on the report, based on its X & Y Position values.

Each Visualization will also have a set of specific, unique options. We can see an example of how this looks by going back to KPI’s again and examining the top four options listed:

  • Indicator: This area provides options on how to display the Indicator field value on the KPI. It is possible to round up figures to the thousands, millions and even trillions, and also to adjust the number of decimal places shown. Auto is the default option used and will generally assign the best rounding option, depending on the underlying value.
  • Trend Axis: Here, it is possible to enable/disable the trend visual on the KPI. The example below demonstrates how this looks when disabled:
  • Goals: With the Goal toggle, it is possible to remove the Goal figure underneath the Indicator value completely; the Distance toggle will remove the percentage distance value as well. It is possible to mix and match options here, as indicated in the example below:
  • Color coding: The default RAG (Red, Amber, Green) traffic light system should be suitable for most situations but, if you have a specific branding requirement, it is possible to override the Good, Neutral and Bad colours with a custom Hex colour value. The Direction option also lets you reverse how the KPI works. So, for example, if raising more than 5 IT service requests would lead to a Bad outcome, any number underneath this would appear as Good instead.

When it comes to this subject area for the exam, there is no need to study the whole range of formatting options available; however, a general awareness will hold you in good stead.

Example: Creating a KPI

The process involved in building out a KPI can be a lot more involved then you may suspect, based on the descriptions provided so far. Therefore, the example that follows is designed to show you the type of preparatory modelling steps that will be needed to put in place a working solution utilising KPIs. To follow through these steps, make sure you have connected Power BI up to the WideWorldImporters sample database and that the Sales.OrderLines table is within your Power BI data model:

  1. In Power BI Desktop, on the Sales OrderLines table, use the New Column button to add on the following new calculated columns:
    • GrossPrice = ‘Sales OrderLines'[UnitPrice] * ‘Sales OrderLines'[Quantity]
    • NetPrice = ‘Sales OrderLines'[GrossPrice] + (‘Sales OrderLines'[GrossPrice] * (‘Sales OrderLines'[TaxRate] / 100))
    • OrderDate = RELATED(‘Sales Orders'[OrderDate])
  2. Next, create a Calculated Table via the New Table option, that uses the following DAX formula:
    • SalesOrderAgg2016 = FILTER(SUMMARIZE(‘Sales OrderLines’,’Sales OrderLines'[OrderDate], “Total Gross Price”, SUM(‘Sales OrderLines'[GrossPrice]), “Total Net Price”, SUM(‘Sales OrderLines'[NetPrice])), ‘Sales OrderLines'[OrderDate] >= DATE(2016, 1, 1) && ‘Sales OrderLines'[OrderDate] <= DATE(2016, 12, 31))
  3. Doing this will create a new table object that looks similar to the below, providing a total sum of the calculated columns created in step 1), grouped by OrderDate. I would also recommend, at this stage, to change the format of the new columns to your preferred Currency value:
  4. Add a new Calculated Column to the SalesOrderAgg2016 table to get the Month Name label from the OrderDate column:
    • OrderDateMonthName = FORMAT(DATEVALUE(SalesOrderAgg2016[OrderDate]), “MMMM”)
  5. With the base data ready, we can now look at creating an Actual and Target Measure on the SalesOrderAgg2016 table, using the following DAX formulas:
    • Actual Total Net Price = SUM(SalesOrderAgg[Total Net Price])
    • Target Total Net Price = 5750000
    • Actual Total Gross Price = SUM(SalesOrderAgg[Total Gross Price])
    • Target Total Gross Price = 5500000
  6. On the Report tab, under the Visualizations area, click on the KPI icon to add an empty KPI visualization to the report:
  7. Click on the newly created Visualization and, under the Fields area, drag and drop the appropriate SalesOrderAgg2016 fields into the wells indicated below:
  8. The visualization will then update accordingly, showing us the figure trend over each month and coloured red to indicate that this KPI is currently off target:
  9. To see how a KPI looks when ahead of target, we can repeat steps 5) and 6), but this time using the Net figures instead:

Key Takeaways

  • There are two principle visualization types available within Power BI to help track actual-to-target progress – KPIs and Gauges.
  • KPIs provide a more visually unique means of a binary success/fail determination when tracking towards a target. It is also possible to use KPI’s to track variance over time via the Trend axis. The Indicator will typically be the result of some form of aggregation or Measure.
  • Gauges provide a less visually distinctive, but non-binary, mechanism of viewing progress towards a target. Gauges support more potential field well values when compared with KPIs, nearly all of which are optional in some way. You can also manually define some of these values, for situations where your data model does not contain the required information.
  • All visualizations within Power BI are modifiable from a display or formatting perspective. The same basic options will generally be supported – such as changing a font type or background colour – with more specific configuration properties available per unique visualization type. For example, a KPI visualization can be customised to hide the background Trend Axis entirely. All of these options are designed to give developers greater control over the look and feel of their reports and to mirror them as closely as possible to any potential branding requirement.
  • When building out a solution designed to monitor progress within Power BI, the steps involved will typically be more in-depth than merely creating a new visualization. In most cases, there will be a requirement to bring together a lot of the other skills that have been discussed previously within this series – such as creating DAX formulas, modifying data within Power Query or bringing together different data sources into a single model. It is essential, therefore, not to underestimate the amount of time and effort involved in creating a practical solution that takes advantage of KPIs or Gauges.

I hope that this weeks post has been a little easier to bear when compared with DAX 🙂 . In next weeks post, we will take a closer look at data hierarchies and how to apply these to visualizations within Power BI.

Welcome to post number 6 in my series concerning Microsoft Exam 70-778. The series aims to provide a revision tool for those who are looking at taking the exam and to also provide an introduction into some of the fundamental concepts around Power BI. As alluded to previously on the blog, Power BI is increasingly a topic that Dynamics 365 Customer Engagement professionals need to grapple with, particularly if they wish to implement a reporting solution that helps to ensure the long-term success of their CRM deployments. Going for Exam 70-778 gives you an ideal opportunity to familiarise yourself with this exciting technology area.

We moved into the Modeling and Visualizing Data theme for the first time last week, and we now jump into a relatively big subject this week – Create calculated columns, calculated tables, and measures. The skill areas covered here are as follows:

Create DAX formulas for calculated columns, calculated tables, and measures; Use What If parameters

To follow on as part of the examples that follow, make sure you have downloaded the WideWorldImporters sample database and hooked up Power BI Desktop to the tables described in my first post in this series.

Now, to begin this week’s post, we must first ask ourselves an essential question:

What is DAX?

Data Analysis Expressions, or DAX, has its roots in SQL Server Analysis Services (SSAS). First released as part of the PowerPivot for Excel 2010 Add-in, DAX has quickly become the preferred language to use when working with Excel PowerPivot, SSAS and – most importantly – Power BI. DAX bears a lot of similarity to standard Excel functions, but a critical differentiator is that DAX does not operate in the context of cells and is considered a strongly typed language (i.e. can lead to a higher risk of errors at runtime). A benefit of using DAX over PowerQuery/M is that the language handles a lot of data conversions implicitly. It also supports the usual list of data types associated with SQL type databases – Decimal/Whole Numbers, DateTime, Text, Boolean etc. A successful Power BI Reporting solution typically relies heavily on the building of complex DAX formulas, which are then complemented by the most appropriate visualisation at the report level.

Context in DAX

An important concept to grapple with DAX is that of context, and how this applies to the various formulas that you build out. There are two types of context:

  • Row Context: Essentially meaning the “current row”, functions that operate in this manner will be processed for every single row on your dataset, outputting the result required each time. For example, the IF function works on a row context.
  • Filter Context: Functions that support this will take into account any filters defined at the report level when performing the relevant calculation. Consequently, a lot of the work involved in contextually updating visuals is handled automatically by your DAX formulas, with no need to configure specific Measures/calculated columns as a workaround.

It is essential to understand these two types of context and on how they can impact on each other (the “context transition“). More critically, it’s also crucial to be aware of when a DAX function ignores a context completely. For example, some aggregation functions completely ignore row context. This article on the SQL BI website is a great resource that discusses this whole topic in depth.

Utilising DAX in Power BI

The Modeling tab is your go-to area when working with the three types of DAX calculations within Power BI Desktop – MeasuresCalculated Columns and Calculated Tables:

The sections that follow will discuss each of these in further detail, but there are some useful points to highlight that apply when using DAX generally:

  • Unlike the M Query Editor, there is full Intellisense support provided for DAX within Power BI. Straightaway, this makes this a much more streamlined tool that should be gravitated towards automatically as a consequence.
  • You should always use fully-qualified names that reference both the table object and field name that you are attempting to access. An example of how this should look can be seen below with the custom column EditedByDay on the WideWorldImporters Sales.OrderLines table:
  • It is also possible to add comments to your DAX code, with three flavours on offer; this is the part where we distinguish between the C# and SQL Server developers 🙂 :
    • Single Line Comments: // or
    • Multi-Line Comments: /* and */
  • The screenshot below indicates how to utilise these as part of a DAX calculated column:

Let’s take a look now at the specific areas referenced above in greater detail:

Measures

Measures are best thought of as being fixed (or scalar) values, typically aggregations based on simple or more complex underlying formulas. A straightforward example could be just a Count of all rows on a table e.g

  • SalesOrderLinesCount = COUNTROWS(‘Sales OrderLines’)

Although Measures are compatible with any Visualization, the most common place to find them will be in a Card, as indicated below:

When first working with Measures, an advantageous feature at our disposal is the New Quick Measure option, which provides a softer, GUI-focused approach to creating common types of Measure:

Beyond this, the sky is the limit when working with DAX Measures, with a whole range of different functions that can be used in isolation or tandem with each other.

Calculated Columns

We saw how it is possible to perform simplistic custom/calculated columns using the Power Query Editor as part of an earlier blog post. The functionality offered here can prove useful for straightforward requirements – for example, if Column1 equals “AAA”, then TRUE, else FALSE – but may soon become insufficient if you have more advanced needs for your new column. In this scenario, DAX calculated columns could come to the rescue and give you the ability to more familiarly define your business logic, particularly for those with their Excel head on.

A key consideration when working with DAX Calculated Columns is understanding the impact they have on performance; specifically, that they consume RAM and disk space within your report. You should be aware also of the concept of circular dependencies. It is possible to build highly complex formulas within DAX, that may reference other DAX columns that you have built out. As a consequence, attempting to modify any dependent DAX formulas could result in errors. In addition to this, trying to define multiple calculated columns that perform the same operation may also lead to circular dependencies. There is another excellent article on the SQL BI website that jumps into this whole subject area in greater detail and is worth a read.

Similar to Measures, pretty much every type of DAX function listed here (minus ones that return table values) is useful in some way when working with Calculated Columns.

Calculated Tables

The types of DAX functions discussed so far focus on returning a single value – either in the sense of a fixed, often aggregated, value as part of a Measure or a single value on a row-set with a Calculated Column. The next step up from that, and where DAX enters a whole new realm of usefulness, is the ability to define Calculated Tables based on DAX formulas. There are a LOT of options at your disposal here. As well as being able to derive Calculated Tables directly from other objects within your model (e.g. the DAX formula SalesOrderLinesDupe = ‘Sales OrderLines’ would create a copy of the Sales OrderLines table), there is also a wide array of different functions available that, when used individually or in combination, can satisfy any particular requirement:

  • The FILTER function returns a new table object filtered from another, based on a specific value. The CALCULATETABLE function is the “big brother” of this particular function, with the ability to define multiple, potentially complex filter conditions.
    • e.g. The following formula will return all Sales OrderLine records with a PickedQuantity value greater than 7.
  • The ALL function returns all data from a table with underlying filters removed (i.e. filters that are defined using the options indicated in the screenshot below. It can be used to specify a whole table or single/multiple column(s). There are also variants of this, such as ALLEXCEPT (which will return all columns except the ones listed) and ALLNOBLANKROW (which removes any blank rows before returning the data).
  • VALUES and DISTINCT return a list of distinct rows only. VALUES can only work with physical tables, whereas DISTINCT also works with table expressions.
    • e.g. Both of the DAX functions below do the same thing – return 227 distinct Description values from the Sales OrderLines table:
  • SUMMARIZE groups a table by one or more columns, adding new ones where necessary. The grouping is optional. Only rows that contain data will return.
    • e.g. The follow DAX formula will return the total UnitPrice for each Sales OrderLines Description:
  • ADDCOLUMNS returns an existing table with new columns defined, typically as DAX formulas. SELECTCOLUMNS behaves similarly, but you have to specify the columns to return, including any new ones.
    • e.g. SalesOrderLinesWithCalcColumn = ADDCOLUMNS(‘Sales OrderLines’, “IsEndOfMonth”, IF(DAY(‘Sales OrderLines'[LastEditedBy]) >= 25, TRUE(), FALSE())) returns the Sales OrderLines table, with the example IsEndOfMonth column included as a new column based on a DAX formula.
  • TOPN performs ranking based on conditions and returns the specified number of records based on the ranking criteria. Ties can also occur, meaning that the specified ranking number may not correlate to the actual number of rows returned.
    • e.g. The following formula will return the Top 25 Sales OrderLines records by UnitPrice. Notice the number of records returned exceeds 25; this is because of multiple ties within the underlying data.
  • There is a range of functions available to accommodate common table-join scenarios. CROSSJOIN allows for Cartesian product joins from multiple tables; NATURALINNERJOIN & NATURALLEFTOUTERJOIN perform self-explanatory joins, which seasoned SQL Server developers should have no trouble in guessing; and GENERATE & GENERATEALL allow for more bespoke table joins to take place.
    • e.g. the following formula will perform an inner join of the Sales Orders and Sales OrderLines table and return the ID values from both as a new table object
  • GENERATESERIES creates a single table with a list of numerical values. It requires the start & end number and the (optional) increment value.
    • e.g. SeriesExample = GENERATESERIES(1, 150, 1) will generate a single column table object with 150 rows, numbered 1 to 150.
  • CALENDAR generates a list of date/time values in a single table column object, based on the range specified. CALENDARAUTO behaves the same but creates a list of relevant date values after evaluating the whole model (e.g. if there is another table with dates between January 1st and November 31st 2018, then this would be the list of dates generated).
    • e.g. Using CalendarAuto with the WideWorldImporters Sales Orders and Sales OrderLines tables will create date values from the 1st January 2013 through to the 31st November 2016.
  • ROW generates a single row table with the column values specified, based on a key/value pairing.
    • e.g. RowExample = ROW(“My DAX”, “Brings all the nerds to the yard”, “And they’re like”, “Do you wanna write Measures”, “Damn right, I want to write Measures”, “I can teach you, but you must learn M first”) produces the following table:
  • UNION combines two or more tables vertically, retaining duplicates. Column names do not need to match, but the number of columns does. INTERSECT is similar to UNION but maintains only values that exist the same in both tables. Nested UNIONS are usable alongside this, but the specified order can impact the result. EXCEPT is similar to UNION and INTERSECT, but outputs rows that exist only in the first table, not the second one.
  • DATATABLE allows the user to specify tables that contain manually entered data, similar to the Enter Data feature:
    • e.g.:

What If Parameters

There may be times within Power BI where you need to provide some predictive modelling capability. For example, show how potential sales will look based on an increased margin of 25%. Or it could be, as developers, we wish to test the functionality of some of our DAX formulas by having the ability to see how potential ranges of values will affect our calculations. For either scenario, What If Parameters can provide a solution. By default, on creation, a slicer control is provided that can be placed anywhere on your Report. To create a What If Parameter:

  1. Within Power BI Desktop, on the Modeling tab, select the New Parameter option:
  2. On the What-if parameter dialog box, enter the details as indicated below and press OK:
  1. This will then add the following components to your report:
    • A Slicer control called TestWhatIfParameter
    • A new Calculated Table called TestWhatIfParameter:
  2. Under the Visualizations tab, select the Gauge visualization to add it your report. Define the properties for the Gauge visual as follows:
  1. Now, when you adjust the value in the TestWhatIfParameter, the Gauge will update accordingly. For example, setting it to 75 will update the report as follows:

This simple example does not do justice to the potential that this feature has, so I would recommend exploring it further yourself.

Key Takeaways

  • DAX is the primary formula language when working with datasets outside of Power Query. It includes, to date, more than 200 different types of functions that can assist in all sorts of data modelling.
  • An important concept to grasp within DAX is context and, specifically, row context (formulas that calculate a result for each row in a dataset) and filter context (formulas that automatically apply any filtering carried out at report level).
  • The sheer amount of DAX functions available makes it impossible to master and remember all of them, particularly when it comes to the exam. Your learning should, therefore, focus on learning the general syntax of DAX and the general types of functions available (aggregation, date/time etc.)
  • There are three principal means of utilising DAX with Power BI:
    • As Measures: These typically present a scalar value of some description, often an aggregation or a result of a complex formula. Using them in association with a Card visualization type is recommended, but this is not a strict requirement.
    • As Calculated Columns: Similar to the options available within Power Query, Calculated Columns provide a dynamic and versatile means of adding new columns onto your datasets. Compared with the options available within Power Query and the complexity of the M language, DAX Calculated Columns might represent a more straightforward means of adding custom columns onto your datasets.
    • As Calculated Tables: A powerful feature, mainly when used in conjunction with Calculated Columns, you have the ability here to create entirely new datasets within the model. These will typically derive from any existing datasets you have brought in from Power Query, but you also have functionality here to create Date tables, sequence numbers and manually defined datasets as well.
  • What-if Parameters provide of means of testing DAX formulas, as well as allowing report users to perform predictive adjustments that can affect multiple visualizations on a report.

DAX is a subject so vast that there are entire books devoted to it. Therefore, from an exam perspective, don’t worry too much about becoming a DAX master. In next weeks post, we’ll hopefully go down a few gears as we take a look at how to work with KPIs and how to apply them to visualizations.