The New Year is almost upon us, meaning its time to put in place some resolutions for the year ahead. I can think of no better commitment then to learn more about Power BI in 2019, which is hopefully the reason why you are reading this right now ūüôā . Welcome to the eighth post in my series concerning Microsoft Exam 70-778, where I hope to provide a learning/revision tool for anyone who is taking the exam or looking to increase their Power BI expertise. Last week, we investigated how to manage Key Performance Indicator (KPI) reporting using Power BI. We now move into another topic area that is also tied closely to visualizations – Create hierarchies. The related skills for this exam area are:

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

In the sections that follow, I will provide an overview of the two different types of Hierarchies configurable within Power BI, before then providing a step-by-step guide on how to create one. As with previous posts in this series, this subject does bring together some other skill areas, such as importing data with Power Query and using DAX calculated columns. Knowledge of these topics will hold you in good stead when starting with hierarchies.

What is a Hierarchy?

Hierarchies form the cornerstone of a broad aspect of human existence, which makes it natural they are a common theme when it comes to Business Intelligence (BI) solutions. A hierarchy is definable as a logical, often visual, representation of an order of precedence. Hierarchies, in strict Power BI terms, do not differ much from this general definition; they allow developers to order data by preference, priority or anything in between. When configured and used in isolation, they offer minimal benefit to Power BI report consumers. They start to become really valuable when utilised alongside visualizations, providing an additional interaction point for key data points and allowing report users to tailor a visualization to suit their needs. Some potential usage scenarios for hierarchical data may include:

  • Providing top-level and subcategory classifications for product records.
  • Modelling a business reporting line hierarchy, from CEO down to Developer.
  • Splitting sale date by quarter, year or even month.

Hierarchies come in two flavours within Power BI – Date Hierarchies and Custom (or User Defined) Hierarchies. The next two sections delve deeper into the inner workings of each.

Date Hierarchies

Date Hierarchies are best described as a logical breakdown of the constituent components of a date value, based on four different levels:

  • Year
  • Quarter
  • Month
  • Day

For each field that contains a Date Hierarchy, the appropriate value for each of the above is exposed out for utilisation across Power BI Desktop. So, for example, the underlying hierarchy values for the 3rd March 2017 would be:

  • Year: 2017
  • Quarter: Qtr 1
  • Month: March
  • Day: 3

All columns with a data type of Date or Date/Time will have a Date Hierarchy created for them automatically. The hierarchy will then become accessible for use in the following areas of Power BI Desktop:

  • When building out formulas using DAX. For example, the screenshot below shows how it is possible to access the hierarchy field values listed above from the Date/Time field LastEditedWhen:
  • When working with a Table visualization. Adding a Date or Date/Time column into the Values well will automatically create a table containing all of the hierarchy fields. An example of how this looks for the LastEditedWhen field is seen in the image below:

It is possible to disable the automatic creation of Date Hierarchies within the File -> Options area of Power BI Desktop, by toggling the Auto Date/Time option. Take care when toggling this though, as doing so will remove any existing Date Hierarchies within your model:

This fact is confirmed when returning to the Table created using the LastEditedWhen field, which automatically reverts to the base column value when the Auto Date/Time option is disabled:

Custom Hierarchies

For more bespoke requirements, a Custom Hierarchy affords the same kind of functionality discussed in the previous section. They can be created from the right-hand Fields pane by selecting a field within a Power BI table and choosing the New hierarchy option:

Once established, you can then:

  • Rename the hierarchy. By default, it will be named using the convention <Field Name> Hierarchy.
  • Include additional fields by dragging and dropping them into the hierarchy.
  • Reorder the hierarchy, by dragging and dropping the fields into your preferred order, from top to bottom.
  • Delete the hierarchy through right-clicking it and selecting the Delete option. All fields included in the Hierarchy will remain as part of the underlying query.

A Custom Hierarchy typically relies on a self-relationship that a query might have on itself. An excellent example of this, already alluded to, is an Employee table, where an individuals Manager is trackable via a ManagerID column, that resolves back to an EmployeeID record. In these cases, there are several DAX functions available that will assist in getting custom columns set up to support your hierarchy, described collectively as Parent and Child Functions. Usage of these functions is essential when building out Hierarchies involving parent/child relationships.

The exercise at the end of this post will go through the detailed steps involved in creating a Custom Hierarchy.

Using Hierarchies with Visualizations

A good reason to include hierarchies as part of your Power Bi Reports is the options they unlock from a data drill-down perspective. We’ve seen already the behaviour Date Hierarchies adopt when using the Table visualization, which offers nothing in this respect. Other visualization types support, by comparison, far richer options for “homing in” on a particular piece of data, epitomised by the following buttons that appear at the top of the supported visualization:

In order, from left to right, these let you:

  • Drill up your data one level in the hierarchy.
  • Enable the Click to turn on Drill Down feature, allowing you to click on any part of a visual to drill-down to the next level of the hierarchy.
  • Go to the next level in the hierarchy.
  • Expand all down one level in the hierarchy. This behaves differently to the Click to turn on Drill Down option mentioned already, by grouping together the previous hierarchy level each time you drill-down.

An example of how all this works as part of a Pie chart visualization is viewable in the sequence below:

As should hopefully be clear, hierarchies are very much the icing on the cake in building an engaging and wholly interactive reporting solution within Power BI.

Example: Creating a Hierarchy and adding it to a Visualization

The steps that follow provide instructions on how to build out a product categorisation hierarchy, using sample data, and then how to apply this to a Donut chart visualization. All that is required to work through these steps is access to the Power BI Desktop application:

  1. In Power BI Desktop, click on Edit Queries to open the Power Query Editor. Navigate to the New Source button and select the Blank Query option:
  2. Select the newly created Query1 in the Queries pane and click the Advanced Editor button. Copy and paste the following M code into the window and press Done:
    1. let
          Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZNNbsMgEIWvgrxqJRb438mybZZRonrpZoGScYNiYwvsqu11epOerBjstHKwE3WBhJj3vjdoIMsc18HO4/MabXjBOCzRijcgasEkoDtJy7qA+5sUgVrfXwEOCXF2OHO8kWsrqhykZBWnxRR5SmPYHo6jRLP9kS9tKD9QcZji2uqGGeE4cTUz6D0piDe2v7i85ThSS1tDtVm974+Uv0Kf+Vc3Uzojus0mz7sEPwptafPVgROPlC1rJinj2sBIfnVPIE9NVVsIlsrgX1yq8kqgNTtI9gnopSXEiyZe0X99iZ5mgheumaZL7LC0pEWBHlqpBiGlenBQsra80sF1k4kPfM+Ed38qPVIB24rxxjL32WKkYS4h2Pf623SfKT191KD7OXdiAd8iMgEhDn2F3/0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductID = _t, Name = _t, #"Product ID" = _t, Parent = _t, TotalSales = _t]),
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}, {"Name", type text}, {"Product ID", type text}, {"Parent", Int64.Type}, {"TotalSales", Currency.Type}}),
          #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"TotalSales"})
      in
          #"Replaced Value"
      
  3. This will then load up a table object that resembles the below, which derives from the sample Product data from Dynamics CRM/365 Customer Engagement:
  4. Right-click the Query1 query and use the Rename option to change its name to Products. Select the Close & Apply button to exit the Power Query Editor.
  5. Open the Data pane, select the Products query and use the following DAX formulas to create New Columns for the table:
    • ProductHierarchy = PATH(Products[ProductID], Products[Parent])
    • Product Category = LOOKUPVALUE(Products[Name], Products[ProductID], PATHITEM(Products[ProductHierarchy], 1, INTEGER) )
    • Product Grouping = LOOKUPVALUE(Products[Name], Products[ProductID], PATHITEM(Products[ProductHierarchy], 2, INTEGER) )
    • Base Product = LOOKUPVALUE(Products[Name], Products[ProductID], PATHITEM(Products[ProductHierarchy], 3, INTEGER) )
  6. The first field creates the required hierarchy list in a delimited format; the remaining columns then list out the three levels of the hierarchy as separate column values. The data in your table should resemble the below if done correctly:
  7. Right-click the Product Category field in the Fields pane and select New Hierarchy, to create a new hierarchy as indicated below:
  8. Modify the newly created hierarchy so that:
    • It is named Product Hierarchy
    • It contains, in addition to the Product Category field, the Product Grouping and Base Product fields.
    • The order of the newly added fields reflect the following:
      • Product Category
      • Product Grouping
      • Base Product
  9. If done correctly, the Hierarchy should resemble the following:
  10. Click on the Report tab and add an empty Donut chart visualization to the report. Populate the Field well values as shown below:
  11. The Donut chart should update accordingly, and with the appropriate drill-down options available at the top of the visualization. Notice also that hovering over each section of the Donut chart displays an aggregated total for that level of the hierarchy:

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.

The past couple of posts have already started to introduce some of the visualization options available within Power BI. In next week’s post, we will take a much closer look at all of the options available within the application to display data, as well as look at features such as Bookmarks and the various report page customisation tasks available to developers when building out a report.

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 РMeasures, Calculated 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.

The life of a Dynamics CRM/Dynamics 365 for Customer Engagement (CRM/D365CE) professional is one of continual learning across many different technology areas within the core “stack” of the Business Applications platform. Microsoft has clarified this in no uncertain terms recently via the launch of the Power Platform offering, making it clear that cross-skilling across the various services associated with the Common Data Service is no longer an optional requirement, should you wish to build out a comprehensive business solution. I would not be surprised in the slightest if we find ourselves in a situation where the standard SSRS, Chart and Dashboarding options available within CRM/D365CE become deprecated soon, and Power BI becomes the preferred option for any reporting requirements involving the application. With this in mind, knowledge of Power BI becomes a critical requirement when developing and managing these applications, even more so when you consider how it is undoubtedly a core part of Microsoft’s product lineup; epitomised most clearly by the release of the Microsoft Certified Solutions Architect certification in BI Reporting earlier this year.

I have been doing a lot of hands-on and strategic work with Power BI this past year, a product for which I have a lot of affection and which has numerous business uses. As a consequence, I am in the process of going through the motions to attain the BI Reporting MCSA, having recently passed Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel. As part of this week’s post, I wanted to share some general, non-NDA breaching advice for those who are contemplating going for the exam. I hope you find it useful ūüôā

Power BI Experience is Relevant

For an exam focused purely on the Excel sides of things, there are a lot of areas tested that have a significant amount of crossover with Power BI, such as:

  • Connecting to data sources via Power Query in Excel, an experience which is an almost carbon copy of working with Power Query within Power BI.
  • Although working with the Excel Data Model, for me at least, represented a significant learning curve when revising, it does have a lot of cross-functionality with Power BI, specifically when it comes to how DAX fits into the whole equation.
  • Power BI is even a tested component for this exam. You should, therefore, expect to know how to upload Excel workbooks/Data Models into Power BI and be thoroughly familiar with the Power BI Publisher for Excel.

Any previous knowledge around working with Power BI is going to give you a jet boost when it comes to tackling this exam, but do not take this for granted. There are some significant differences between both sets of products (epitomised by the fact that Excel and Power BI, in theory, address two distinctly different business requirements), and this is something that you will need to understand and potentially identify during the exam. But specific, detailed knowledge of some of the inner workings of Power BI is not going to be a disadvantage to you.

Learn *a lot* of DAX

DAX, or Data Analysis Expressions, are so important for this exam, and also for 70-778 as well. While it will not necessarily be required for you to memorise every single DAX expression available to pass the exam (although you are welcome to try!), you should be in a position to recognise the structure of the more common DAX functions available. You ideal DAX study areas before the exam may include, but is not limited to:

A focus, in particular, should be driven towards the syntax of these functions, to the extent that you can memorise example usage scenarios involving them.

Get the exam book

As with all exams, Microsoft has released an accompanying book that is a handy revision guide and reference point for self-study. On balance, I feel this is one of the better exam reference books that I have come across, but beware of the occasional errata and, given the frequency of changes these days thanks to the regular Office 365 release cycle, be sure to supplement your learning with any proper online cross-checking.

Setup a dedicated lab environment

This task can be accomplished alongside working through the exercises in the exam book referenced above but, as with any exam, hands-on experience using the product is the best way of getting a passing grade. Download a copy of SQL Server Developer edition, restore one of the sample databases made available by Microsoft, get a copy of Excel 2016 and – hey presto! – you now have a working lab environment & dataset that you can interact with to your heart’s content.

Pivot yourself toward greater Excel knowledge

Almost a quarter of the exam tests candidates on the broad range of PivotTable/PivotChart visualisations made available within Excel. With this in mind, some very detailed, specific knowledge is required in each of the following areas to stand a good chance of a passing grade:

  • PivotTables: How they are structured, how to modify the displaying of Totals/Subtotals, changing their layout configuration, filtering (Slicers, Timelines etc.), auto-refresh options, aggregations/summarising data and the difference between Implicit and Explicit Measures.
  • PivotCharts: What chart types are available in previous and newer versions of Excel (and which aren’t), understanding the ideal usage scenario for each chart type, understanding the different variants available for each chart types, understanding the structure of a chart (Legend, Axis etc.), chart filtering and formatting options available for each chart.

Check out the relevant edX course

As a revision tool, I found the following edX course of great assistance and free of charge to work through:

Analyzing and Visualizing Data with Excel

The course syllable mirrors itself firmly to the skills measured for the exam and represents a useful visual tool for self-study or as a means of quickly filling any knowledge gaps.

Conclusions or Wot I Think

It is always essential, within the IT space, to keep one eye over the garden fence to see what is happening in related technology areas. This simple action of “keeping up with the Joneses” is to ensure no surprises down the road and to ensure that you can keep your skills relevant for the here and now. In case you didn’t realise already, Power BI is very much one of those things that traditional reporting analysts and CRM/D365CE professionals should be contemplating working with, now or in the immediate future. As well as being a dream to work with, it affords you the best opportunity to implement a reporting solution that will both excite and innovate end users. For me, it has allowed me to develop client relationships further once putting the solution in place, as users increasingly ask us to bring in other data sources into the solution. Whereas typically, this may have resulted in a protracted and costly development cycle to implement, Power BI takes all the hassle out of this and lets us instead focus on creating the most engaging range of visualisations possible for the data in question. I would strongly urge any CRM/D365CE professional to start learning about Power BI when they can and, as the next logical step, look to go for the BI Reporting MCSA.

The single biggest challenge when developing a reporting solution is data. Invariably, you won’t always have one database that contains all the information you need; often, you will need to bring across disparate and unrelated data sources into a common model. This problem can be exasperated if your organisation has a number of application or database systems from different vendors. Finding a tool that can overcome some of these hurdles is a real challenge. For example, whilst I am a huge fan of SQL Server Reporting Services, the out of the box data connection options are generally limited to common vendor products or ODBC/OLE DB data sources. Case in point: Finding or even developing a Data Source that can support a JSON stream can be troublesome and complicated to configure. With the landscape as such, the reporting tool that can offer the most streamlined method of overcoming these challenges is the tool that is going to win the day.

Following on from my deep-dive with the product last year, I have been working more and more with PowerBI in recent weeks. What I like most about the tool is that a lot of the hassle is taken out of configuring your data sources. PowerBI does this by leveraging the existing Power Query language and equipping itself with a large number of Data Source Connectors. The most surprising aspect of this? Microsoft products form only a subset of the options available, with connectors in place for many of competitor products from the likes SAP, SalesForce and Oracle. In my limited experience with the product to date, I have yet to find a data source that it does not support, either as part of a data connector or a manual Power Query.

A recent work example can best illustrate the above, as well as showcasing some of the built-in functionality (and learning curves!) that come to working with data via Power Query and writing Data Analysis Expressions (DAXs). There was a requirement to generate an internal department dashboard for an IT service desk. The dashboard had to meet the following key requirements:

  • Display a summary of each team members movements for the current week, ncluding the location of each person on that current day. Each member of the team was already recording their weekly movements within their Exchange calendar as all day appointments, configuring the Subject field for each appointment accordingly. For example,¬†In Office,¬†Working at Home etc. No other all day appointments were booked in the calendars.
  • Query Dynamics CRM and return data relating to Active/Inactive Case records.
  • To be displayable on a TV/Screen, refresh automatically¬†and be exportable¬†as a .pdf document or similar.

A CRM Dashboard can achieve about 50-60% of the above, but the key requirements of querying Exchange and exporting the dashboard are much more tricky; whilst it is certainly possible to perform web service requests from within CRM to Exchange, the process would be so convoluted to implement that is arguably not worth the effort. Likewise, CRM is not particularly friendly when it comes to printing out Dashboards, as you often left to the mercy of the web browser in question. With all this in mind, we decided that PowerBI was the best option and proceeded to bring all the data together using PowerQuery.

We first used the out of the box Exchange connector to query each person’s mailbox for all Calendar items, performing two transformations on the data. First, we filtered the result to return Calendar items from the current week and, second, we added a column to identify which Calendar the record derives from (as there is no field on each record to determine this). We’ll see why this is required in a few moments:

let
    Source = Exchange.Contents("john.smith@domain.com"),
    Calendar1 = Source{[Name="Calendar"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Calendar1, each Date.IsInCurrentWeek([Start])),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Owner", each "John Smith")
in
    #"Added Custom"

Next, we combined all Calendars together into one table – again using Power Query. Table.Combine is a comma separated list of all tables you want to merge together, so you can add/remove accordingly to suit your requirements. We also take this opportunity to remove unnecessary fields and convert our Start and End field values to their correct type:

let
    Source = Table.Combine({Calendar1, Calendar2, Calendar3}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Folder Path", "Location", "DisplayTo", "DisplayCc", "RequiredAttendees", "OptionalAttendees", "LegacyFreeBusyStatus", "IsReminderSet", "ReminderMinutesBeforeStart", "Importance", "Categories", "HasAttachments", "Attachments", "Preview", "Attributes", "Body"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Start", type date}, {"End", type date}})
in
    #"Changed Type"

Our CRM data is returned via an adapted version of the query used previously on the blog, taking into account the benefits of using a Saved Query as opposed to FetchXML. No further work is required to manipulate the data once in PowerBI, so this won’t be covered any further. Our issue is now with the Exchange Calendars. Because the appointments in the Calendars to indicate each persons movement are set as All Day appointments spanning multiple days, we have no way of extrapolating the days¬†in between to determine whether it is the current day. So for example, if the all day Appointment starts on Monday and ends on Wednesday, we have Monday and Wednesday’s date, but not Tuesday’s. We, therefore, need to find a solution that determines whether the appointment falls on a specific day of the week – Monday, Tuesday, Wednesday, Thursday or Friday.

Our first step is to generate a date table covering the entire period we are concerned with. Using this very handy query, we can set up a PowerBI function that will allow us to generate just that – in this case, for the whole of 2017:

Why do we need this? Because we need to determine for each date in 2017 what day it falls on. For this reason, we now take off our Power Query hat and jam on our DAX one instead ūüôā Close & Apply your queries in PowerBI and then navigate to your new date table. Add a new Column, using the following DAX formula to populate it:

Day of Week (Number) = WEEKDAY('2017'[Date], 2)

The WEEKDAY function is an incredibly handy function in this regard, enabling us to determine the day of the week for¬†any¬†date value. Nice! We can now go back to our “unified” calendar, and perform the following modifications to it:

  • Add on a column that returns a TRUE/FALSE value for each row on our calendar, which tells us if the Start, End or any date between these values falls on a specific day. So, for our IsMondayAllDay field, our DAX formula is below. This will need to be modified accordingly for each subsequent column, by incrementing 1 on the ‘2017’[Day of Week (Number)], 1¬†bit by 1 for Tuesday, 2 for Wednesday etc.:

IsMondayAllDay = IF(AND(CONTAINS(CALENDAR([Start], IF([End] = [Start], [End], [End] - 1)), [Date], DATEVALUE(LOOKUPVALUE('2017'[Date], '2017'[Week Number], FORMAT(WEEKNUM(AllCalendars[Start], 2), "General Number"), '2017'[Day of Week (Number)], 1))), AllCalendars[IsAllDayEvent] = TRUE()), "TRUE", "FALSE")

  • A calculated column that tells us whether the current row is today, by referencing each of our fields created in the subsequent step. Similar to above, a TRUE/FALSE is returned for this:

IsToday = IF(([IsMondayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 1) || ([IsTuesdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 2) || ([IsWednesdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 3) || ([IsThursdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 4) || ([IsFridayAllDay] = "True" && WEEKDAY(TODAY(), 2) = 5), "TRUE", "FALSE")

We now have everything we need to configure a Measure that can be used on our Dashboard – the Subject of the calendar appointment and a way of indicating that the appointment is today. So our final DAX formula would be as follows for John Smith:

John's Location (Today) = LOOKUPVALUE(AllCalendars[Subject], AllCalendars[IsToday], "TRUE", AllCalendars[Owner], "John Smith")

Now, it is worth noting, that the above solution is not fool-proof. For example, if a person has multiple All Day Appointments configured in their Calendar, then it is likely that the Measure used above will fall over. Giving that this is unlikely to happen in the above scenario, no proactive steps have been taken to mitigate this, although you can certainly implement a solution to address this (e.g. use the MAX function, only return records which contains “Working”, “Office” or “Home” in the Subject etc.). Nevertheless, I feel the above solution provided an effective “crash course” in a number of fundamental PowerBI concepts, including:

  • PowerQuery data retrieval and manipulation
  • PowerBi Functions
  • DAX Formulas and the difference between Measures and Calculated Columns

As a colleague recently said to me, “I think we will be using PowerBI a lot more in the future”. This is something that I would certainly agree with based on my experience with it so far ūüôā