• A Power BI solution gets built and delivered to the business.

• Some time passes, and the Power BI developer moves on to other things. In doing so, they lose familiarity with the logic of the report.

• The business users notice that the report is not calculating values as expected, and notifies the BI team.

• A developer (either the original one or a different developer) digs in to the report, and takes a long time to diagnose and correct the issue.

While the business is pleased when the issue is resolved, the issue impacts the overall trustworthiness of the reports provided to decision makers. This can become a real roadblock to creating and maintaining a data-driven culture within the organisation.

And these issues can accumulate if Power BI developers don't follow good development practices. Over time, the BI team may become trapped in firefighting mode, with most of their time spent on fixing reports that keep breaking, instead of building new solutions to support the business.

How do you stop this from happening?

Like with many things, prevention is better than cure.

In my experience, following the DataOps Principles as closely as possible is an effective means for delivering effective and robust analytics solutions.

For the scenario above, the following two DataOps Principles are particularly relevant:

• Quality is paramount

  “Analytic pipelines should be built with a foundation capable of automated detection of abnormalities and security issues in code, configuration, and data, and should provide continuous feedback to operators for error avoidance.”

• Monitor quality and performance

  “Our goal is to have performance, security and quality measures that are monitored continuously to detect unexpected variation and generate operational statistics.”

Essentially, these principles speaks to the need to minimise the technical debt caused by ‘waiting for something to break, and dealing with it only when someone in the business complains’. I see this attitude taken all too often, especially when the team feels they are too busy to follow these principles. Ironically enough, it is usually the case that they are so busy becuase they’re cleaning up messes that could have been avoided by following these ideas in the first place!

John Kerski has some amazing articles about implementing DataOps in Power BI. I would highly recommend checking it out if you commonly deal with the scenarios described above in your organisation.

Ok great. But seriously, how do you stop this from happening?

Alright, I’ll get off my soapbox and get to some practical strategies :)

Solving with simple error checks

A simple way I've found to ensure that quality is always maintained is to use error checks throughout my Power Query logic.

I've worked with a lot of solutions requiring csv extracts rather than databases recently ¹ , and there can often be instances where the source system isn't capable of enforcing data integrity regarding business rules. So adding these error checks has helped me ensure that, if any business rules are violated, the report does not even refresh—stale data is better than incorrect data. ²

As an example, let's consider a set of employee work patterns, which indicate how many hours each employee works on each weekday...

Work patterns are usually recorded cyclically, and in different organisations they may repeat on a weekly, fortnightly or four-weekly basis. We’ll assume a simple weekly work pattern in this example.

In the source system that tracks these patterns, the staff FTEs get entered manually based off of a 40-hour work week ³ . The table above shows these manually entered figures, where the FTE's for Alice and Snehal correctly match the hours worked throughout the week. But for Lindsay and David, their work patterns do not match their weekly hours⁠—Lindsay works 8 hours every day, so his FTE should be 1.0 not 0.8. And David only works 4 days a week, so he should have 0.8 FTE not 1.0.

If we want to enforce our business rule that the FTE must be consistent with the weekly work hours, we can intentionally raise an error when this happens, so that any scheduled refreshes will fail and the incorrect data isn’t brought into our data model.

Power Query has a nifty function called Error.Record that helps us accomplish this. Rick de Groot has a very comprehensive documentation article on all the ways you can use the function, as described here.

To set up our error checking workflow, we can bring the work pattern data into Power Query and add a nested calculation that does the following:

• For each row, sum up the work hours for the week, and convert it into an FTE

• Filter down to only rows where this FTE check doesn't match the manually-entered FTE

• Count how many times this data entry error happens

• Return a descriptive error message if any data entry errors were detected

• If no error were detected, return the original table

Here's the error that gets returned when we apply this logic in Power Query….

And here's the full M code for this scenario…

If these errors get introduced to a published dataset rather than one in Power BI Desktop, you'll get an error message displayed in the Power BI Service that instructs you on how to resolve it.

This can help you to diagnose and potentially correct the issue without even having to open up the report in Power BI Desktop.

Extending to be more descriptive

If there is only one mismatching FTE value, it's helpful to also return the Employee ID for the offending record in the error message, to speed up troubleshooting. And when there is more than one mismatch, you can return a count of the mismatches, and a list of the offending Employee IDs.

Here's how you can extend the logic, and the messages returned for both of the above scenarios… 

You could also fork the offending rows off into a separate query for inspection. Reza Rad has a good post on this exception reporting approach if you want to go even deeper into this topic.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

1. My preference is always for a database connection, but third party SaaS providers rarely like to expose their databases to customers directly. ↩

2. Ideally you would also set up monitoring for refresh failures to proactively deal with these issues. That is beyond the scope of this article. ↩

3. Note that in practice, it would be appropriate to ignore the manually-entered FTE column and calculate the FTE off of the work hours. But bear with me, as this example is just to illustrate the idea of implementing error checks.  ↩

Using Field Parameters, you can effectively set up an experience similar to PivotTables and PivotCharts in Excel. While Power BI has always had a drag-and-drop report interface that is comparable to Excel, many business users still prefer sticking with what they know in Excel. But by using Field Parameters, I’ll show you how you can set up a visually-similar experience to Excel PivotTables. Providing a familiar interface to your die-hard Excel users can help make the move to Power BI much more seamless for them.

Here’s what we’ll be setting up…

This report gives users the chance to visualise a dataset with any combination of line and column charts they’d like.

And for users who prefer a PivotTable, we can also build this…

You can play with the report for yourself here, or read on to learn how to build these for yourself.

Building out the basics

To start building out our customisable chart, let’s first look at our model. We can use the AdventureWorks dataset, which is a basic star schema with measures like Sales, Cost of Goods Sold, Profits, etc. The measures are contained in a disconnected table called ‘Sales Measures’.

We can build out our chart by defining four separate Field Parameters:

1. ‘Chart Axis’, which contains fields from the dimensions that the user might want to display along the x-axis of the chart.

2. ‘Chart Legend’, a copy of ‘Chart Axis’ which lets users choose what is shown on the legend.

3. ‘Column Measure’, which contains measures against the ‘Sales’ fact table to display as the columns of the chart.

4. ‘Line Measure’, a copy of ‘Column Measure’ which lets users choose what measure is shown as an overlayed line chart.

Here are the DAX definitions for ‘Chart Axis’ and ‘Column Measure’. The other two parameters are defined similarly.

Then we can add a Line and stacked column chart visual on to the canvas. Note that it needs to be a combo chart if you want the flexibility to fully customise what kind of chart you end up with.

We can now drag each of our Field Parameter columns into the approporate field wells in the Visualizations pane…

In making the visual, you can see that…

• When no selection is made on any of the Field Parameter slicers, it defaults to the first option for each. This doesn’t apply for the Line Measure though — when unselected, it shows all its measures at once.

• The line chart starts out as disconnected dots rather than actual lines. This happens when the X-axis and Legend have the same field selected. Choosing different fields to appear on the axis and legend solves this.

• The chart automatically sorts based on the sort order applied to the field. In the model, the ‘Date’[Financial Year] column is sorted on another column, so sort order is retained. For your own models, you may find strange sort behaviour if you haven’t explicitly defined the column sort orders. Wyn Hopkins has a video explaining this issue in more detail.

This very quickly gets us to the core functionality of the report. For example, we could build a chart showing Sales and Margin by Month, and broken down by Sales Territory…

But there are a few more functional items to fine tune to make the user experience better.

Showing and hiding chart elements

Showing all the chart elements at once can be overwhelming, so we’d like the option to omit some of them. This would allow us to show separate line charts and column charts rather than a combo chart of both.

To enable this, we can edit each of the four Field Parameter definitions in DAX to include a ‘blank’ option. We’ll need to add a custom row into each parameter, with the sort order given as -1 for each, so that option always appears at the top of the slicer.

The trick to getting this working smoothly is…

• The ‘Chart Axis’ and ‘Chart Legend’ parameters need to reference a measure that just returns a blank. See the reference highlighted in red.

• The ‘Column Measure’ and ‘Line Measure’ parameters need to reference a column where all the values are blank. This can just be a one-record table, and in this case the model already has a dummy column in ‘Sales Measures’ that can serve this purpose. See the reference highlighted in maroon.

If you apply columns and measures respectively (rather than measures and columns), then the outcome will still work. But you’ll get an explicit mention in the chart title of what’s missing, and it isn’t the cleanest way to present the visual…

So by setting the parameters according to the DAX above, we can generate visuals like these…

Adding hierarchies to the field selectors

For our Chart Axis and Chart Legend selectors, there is a long list of fields to choose from. Some of the fields even have the same names, e.g. Country may refer to the country of the Sales transaction, the Customer or the Reseller.

To make things clearer, we could rename the selected field names in the DAX definitions for the Field Parameters. But to make it even clearer to users, instead we’re going to create a hierarchy, with the name of the Dimension table acting as the parent item. To do this, we can add a calculated column to our Chart Axis and Chart Legend parameters.

The column definition above parses the Chart Axis Fields column to get the home table name. It then returns that for any row besides the first row, and in that case it just returns the child name “(No Axis)”.

We can do the same for Chart Legend. Then we can add the new Dimension columns into the Axis and Legend slicers to get our hierarchy.

The benefit of this hierarchy approach is that you can easily select multiple fields at one. This lets you drill up and down on the Axis, but the combo chart doesn’t let us drill up and down on a Legend.

If you want to limit the user’s ability to select multiple Legends at once, you can change the standard slicer to a Hierarchy Slicer custom visual. I’ve found that visual to be somewhat unstable though, where the slicer selections on one slicer can disappear when selecting another slicer. So sticking with the standard slicers would be preferable to this unstable behaviour.

Cleaning up the report page

With some rearranging of visuals, tweaks to the slicers and a few text boxes, we arrive at a nice clean view that evokes the Excel PivotChart interface…

Making a matrix the looks like a PivotTable

We can also build a PivotTable interface with a matrix visual, by using the baseline logic that we’ve already built up…

I’ve intentionally changed the default formatting of the matrix visual to make it look closer to the PivotTable that Excel users are used to.

For the report page above, I’ve repurposed the Chart Axis and Chart Legend parameters to select the matrix Rows and Columns. To ensure that the slicer options still make sense, I’ve adjusted the DAX in the Field Parameters so that the “(No Axis)” option now simply says “(No Selection)”, and similarly for the other slicers.

Since the matrix visual has only one field well that can accept measures, I’ve used just the Column Measures parameter in the matrix and removed the Line Measures slicer.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

The most common use cases I’ve seen thus far tend to involve showing different columns based on a slicer selection. But I'm sure that in the coming weeks and months, the Power BI Community will uncover more elaborate and useful techniques using Field Parameters beyond just this. Here’s one such new application I’ve found…

I had a particular requirement from a client, in which their Project Management Office (PMO) wanted to see two Gantt charts, with different hierarchies on the rows of each:

• One showing the standard Portfolio-Program-Project hierarchy that a PMO considers, and

• Another showing each Business Unit (BU), and each of the BU’s projects

They also wanted to be able to swap between each Gantt chart using a slicer.

I had been exploring this just before Field Parameters were released, and it could be done with a Bookmark Navigator. However, I've discovered that (after a bit of tweaking) it can also be done using Field Parameters used against a matrix visual.

 Here's what the end result looks like, read on to find out how to implement it.

Creating a Gantt chart with a matrix visual

Before getting in to the Field Parameters implementation, I’ll show you how to create a Gantt chart with the native matrix visual. I’m by no means the first person to come up with such an approach (I first saw this idea in a video by Sam McKay from Enterprise DNA), but I’ll walk you through how I’ve gone about it. If you already know how to make one of these, feel free to skip ahead to the next section.

When dealing with project management information, information about different initiatives is typically stored in a hierarchy structure. Here's the schema that we'll consider:

In this example, an Initiative can generically refer to a Portfolio, a Program or a Project of work ¹ . For each Initiative, we list the Portfolio, Program and Project (PPP), as well as the owning Business Unit for each Project, and the Start and End Dates. Note as well that the lowest non-blank values in the PPP columns indicates what level of the PPP hierarchy the initiative falls on.

If we want to build a Gantt chart with the standard PPP hierarchy, we can do this with the native matrix visual.

• For the rows, we can just drag in each of the PPP columns to make a custom hierarchy.

• For the columns, we can bring in the Year and Month Name fields from a date dimension. Note that the date dimension should be disconnected from the Initiatives table, since relating the two tables on Start Date or End Date can make the end result ambiguous for the user.

• For the values, we can create a measure that returns 1 if the Initiative is active within the timeframe, or 0 if it is either yet to start or complete.

Here’s the DAX for the measure, and the resulting matrix.

Note that the BlankRowOnMatrix variable in the measure definition is used to filter out the Initiative records where the Program or Project are blank. If we don’t add this logic in, we get something like the matrix below, which would be hard for users to make sense of.

Also note that for the visual layout, we need the grand total row to be showing, which I’ve retitled here to read as ‘All Initiatives’. This is to allow the higher levels of the hierarchy (Portfolio and Program) to display values at the same time as the lowest level of the hierarchy shown (Project).

I'm not aware of a way to show these row total values without also showing the grand total. But even with this limitation, you can set the font and background colour of the grand total row to match the page background colour so you don't notice it.

To make the visual look more like an actual Gantt chart, we can add conditional formatting for the background and font colour of the matrix cells. We can make these both consistent by using a single measure for conditional formatting, and basing the conditional formatting values on that. In the measure below, we’re defining it such that the bars at each hierarchy level in the Gantt chart will have a different colour.

Note that you need to apply conditional formatting to 'Values and totals' in conditional formatting dialogue box. This ensures that each level of the hierarchy can show it’s own bar at the same time.

This gives us the basis for our Gantt chart…

You may have noticed that the measures above include logic for Business Units as well. This allows us to create another Gantt chart based on the Business Unit-Project hierarchy by swapping the fields on the rows of the visual…

Adding a field parameter

If we want to easily swap between the two Gantt charts above, we can create a field parameter that includes the four fields.

You can then manually select which ones you want, with the sort order determined by the order that you make the selection.

Here’s the same PPP Gantt chart as before, but this time created with the Parameter on the rows of the matrix, and with the Field Parameter slicer after selecting Portfolio, then Program, then Project.

Note however that if you don’t select values in the field parameter slicer in the same order as the hierarchy you want, you’ll get an output that can be hard to interpret. For example, if you select the field values in the opposite order (Project, then Program, then Portfolio), you’ll get something like this…

Since this doesn’t give us very useful information, we’d like to ensure that users can make selections from the Field Parameter slicer than can only result in one of the two Gantt charts we made earlier (PPP and Business Unit-Project).

Setting the sort order for the hierarchies

If we want to enforce what hierarchy levels can be added, and the order they get applied, here's the trick… ²

1. Edit the DAX that defines the Field Parameter to include all the row permutations, including repeats. For us, this means we need to repeat the Project row at the bottom, but increment the Parameter Order field to a new value.

2. Add a calculated column to return the name of the hierarchy you want to show.

3. Add a slicer to your report page based on the Gantt Hierarchy field, and set it to 'single select only'.

Here’s our final result:

At the time of writing you unfortunately can’t set a matrix visual to have all levels expanded by default, so you still have to expand each level manually.

The bookmark navigator approach may be better for end users that want all the levels already expanded, but this is a good alternative in situations where performance is critical and you can’t afford to add additional visuals to your report page.

Applying this approach to table visual

You can also use this field parameters pattern to retain the sort order for columns in a table visual, without having the hierarchy-expansion issue that the matrix visual has. The approach (shown below) may not be very useful for this particular data table, but you may find a good application of it for your own use cases.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

1. This schema is more robust than if it were at the grain of one-row-per-Project (with the other fields as attributes), because this structure also supports ragged and unbalanced hierarchies. ↩

2. Kane Synder has an good article on applying a sort order with field parameters, and this approach could be used to solve this problem. However, it involves creating numerous field parameters and linking them together in the model. The approach described in this article uses only a single field parameter. ↩

This was a good starting point, but they also wanted the chart to show who had met their attendance targets. Staff were expected to attend the office for at least 10 days out of every rolling 4 week window.

They also wanted the chart to call out the number of staff who didn’t attend the office at all during the selected period. We settled on the following rules for the chart:

• Highlight the column for staff who attended 0 out of 20 business days as Red.

• Highlight columns for 1 through 9 days of attendance as Yellow.

• Highlight columns for 10 through 20 days of attendance as Green.

Conditional formatting columns

You can use conditional formatting to achieve this. To build this out, let’s look at our underlying data.

In this example, we’ve got an aggregated table called ‘Staff Attendance Snapshot’ that has one row for each of the possible number of days that staff can attend (0 through 20). It then lists how many staff attended for that many days ¹ .

These snapshotted values are provided for multiple weeks. For now, we'll build a chart using only the values for the period ending on 28/02/2021.

To build out our chart, we can use the Days Attended field as the chart axis, and Staff Count field can be used for the chart values (as an implicit measure). It’s also sensible to look at this week by week, so we can add a slicer to our page to select the period of interest.

To apply conditional formatting, we can write a measure that works out which Days Attended value is selected for each column of the chart, and assigns that to a value.

With the chart selected, go to the Format pane and select the conditional format button under Data colours. We’ll format based on the field values returned for our measure…

This gives out our conditionally formatted chart...

What about when targets change?

While this addressed the initial requirement, the client followed up with some further requests.

An additional requirement from the client was that the conditionally formatted targets needed to change based on what week is selected. Attendance targets may change week to week based on lockdowns or relaxing of social distancing limits.

In addition to this, the client also wanted the chart to have a legend describing what each colour corresponds to.

To address both of these requirements, we’ll need to take a slightly different approach. This new approach requires us to add a few new tables to our model

Extending the data model

First, we’ll need to build a table called ‘Target Status’ which contains a list of the labels we want to appear in our legend.

The Target Status Code column will also be used as the Sort By column for Target Status.

Next, we need a table that lists the targets for each period.

We’ve already established that for the 4 week period ending 28/02/2021, staff were expected to attend for 10 of those 20 business days.

But let’s say that a lockdown started on 01/03/2021 that applied to all staff at the company. As such, the next rolling 4 week period for the week ending 07/03/2021 would have a reduced target, since staff aren’t permitted to attend for one of the four weeks. In this case, the target for this new period would be for staff to have attended 5 out of 20 days. In reality, these 5 days would have to have come from their attendances in the last 3 weeks of February.

Here’s what our ‘Weekly Targets’ table would look like…

Finally, we need a date dimension, which we’ll use as a common dimension between the ‘Staff Attendance Snapshot’ table and the ‘Weekly Targets’. Since this only gets used to show values in the period slicer, I’ve just made a simple table with only the dates that appear in the fact table. In more general cases, you’ll want to use a date dimension with a contiguous list of dates.

Once we’ve loaded these tables, we’ll need to model them as follows. The ‘Date’ table is relate to each table via the [Period End] columns, and ‘Target Status’ related to ‘Weekly Targets’ on the [Target Status Code] columns.

Building our new chart

Now that we’ve built out our data model, we can start building our visuals.

First, we need to create a single-select slicer based on ‘Date’[Period Out] to display the staff attendance for one rolling period at a time. If we don’t do this, we risk returning unmeaningful results by double-counting staff members across different periods.

As before, we can start building our chart with Days Attended as our Axis field, and Staff Count as an implicit measure for our Values. For this technique, it’s important to make sure we’re using a stacked column chart as our visual ² .

We need to bring ‘Target Status’[Target Status] into the Legend well in the Format pane. This will allow us to include columns of different colours, while showing each item on the chart legend.

However, when we do this, we get an error.

This happens because there is no way for the filter context from the Target Status column to propagate to the ‘Staff Attendance Snapshot’ table through the relationships in the data model.

Instead, we can author a new DAX measure to apply the filters as intended.

The measure follows this logic…

• It determines what the upper and lower limits of the Target Status for the period we’ve selected in the slicer.

• It then determines which chart column we are evaluating (i.e. which Days Attended values we’re evaluating)

• It works out if the the Days Attended values applies to the Target Status that we’re considering. E.g. If we’re evaluating the Target Status “Staff with no attendance”, then this only applies when we’re evaluating the Days Attended = 0 chart column.

• If the Days Attended value lies in the Target Status range, then it returns the number of staff who attended. Otherwise it returns blank.

When we drag this new measure onto the chart (and update the chart colours in the Format pane), here’s our result…

Now our colours will be presented as before, but with our legend showing. Note that I’ve already changed the sort order of the Target Status field, so that the colour order in the legend matches the order displaying left-to-right in the chart.

Finally, we can see that our target values (i.e. our chart colours) update as we change the rolling 4 week period selected in our Period End slicer.

Extension: Adding a summary table

To finish this off, we can also add a table that tells us the number and percent of staff that fall into each Target Status category.

Out table will contain the Target Status’[Target Status] column, as well as two additional measures.

Now we can see our overall summary of staff attendance in the table, and the full distribution in our colour-coded chart. Note that I’ve added some conditional formatting to the table so that it’s easier to compare each Target Status.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

1. In a real-world scenario, you’ll likely have a transaction table that lists every time a staff member enters the office (based on swipe card access for example). In that scenario, you could use a disconnected table for the number of days attended, and use a custom measure to count up how many days each employee attends for. I’ve previously written about that approach here. ↩

2. If we use a clustered column chart for this technique instead, then the spacing between the resulting columns will look confusing for the report user. ↩

However this situation is a bit trickier — if your date list has gaps where dates are missing, how are you supposed to handle those cases and still get meaningful results? Read on to find out…

The simple (but dangerous) solution — Group By

In the example of school holidays discussed in the last post, getting the holiday start and end dates is straighforward. You can just take your date list, group by the holiday name and return the minimum and maximum dates for each holiday period.

This works fine when your dates are contiguous, as is the case for school holiday periods. But you can come across some important issues to consider when this isn’t the case.

In another example, say we were dealing with a retail store, and we want to know which salesperson had the longest streak of days for which they had the highest sales. If we just look at two week's worth of data, here's the top salesperson for each day…

If we look at the raw data with each salesperson highlighted, we can clearly see that Natasha has the longest streak of 4 days.

However, if we were to apply the same Group By technique as above, here's what we get for our start and end dates for each salesperson's streak…

This output would suggest that it was actually Omar who had the longest streak! But clearly this is incorrect, as it hasn’t accounted for the gaps in Omar's tenure as top salesperson. If we want to get this logic working properly, we'll need to use an approach called Gap and Island analysis.

 What is Gap and Island analysis?

Whenever we're dealing with an ordered sequence of values (like dates or integers), we may have clusters of values that all occur contiguously, which we'll call islands. Between these islands of values in the sequence, we will have missing values, which we'll call gaps.

When we're dealing with event data (i.e. a dataset with a date/time element), there are plenty of scenarios in which we would want to identify these gaps and islands. Besides the top salesperson streak problem above, we may want to know…

• All the periods during which a customer was active or inactive.

• Which dates we are missing data for in an incomplete dataset (e.g. when you’ve got hundreds of daily data extracts).

• How many days a city has gone without a case of COVID community transmission.

Gap and Island analysis provides approaches for answering these questions. There have been articles written previously on how to deal with gaps and islands in SQL, but I've yet to come across any other articles that illustrate how to approach it in Power Query. So let’s tackle this challenge together!

Identifying islands in a list of dates

With that context out of the way, let's jump back to our salesperson example. For us to automatically determine which salesperson had the longest streak, we have to identify all the islands during which each staff member had the highest sales. Then we can look at which island had the longest duration, and this will tell us our winner.

Let's start by just looking at the data for Natasha. To group up her dates into islands, we'll need to do the following transforms…

1. Clean up our data by removing duplicates and sorting the dates chronologically. Note that these steps don't alter our dataset for this example, but if your own datasets are unordered and contain duplicates, then skipping this step may give you incorrect results.

2. Duplicate the Date column, and convert it into an integer. I’ve renamed this column as Date Value.

3. Add an Index Column.

4. Add a new custom column which subtracts Date Value from Index. I've called the new column Island ID because it uniquely identifies each cluster of dates. Whenever two dates in the list are contiguous, both the date value and the index increase by 1, so the value for Island ID is the same for both dates. If there is a gap between the dates, then the date value will jump by more than 1, so Island ID will change to a different value.

5. Group each Island ID, returning the min and max Date for each island. This is the same logic that we initially used, but instead of grouping dates for each staff member, we're grouping them for each island of each staff member.

6. Remove the Island ID column, since we only needed it to perform this grouping.

 Expanding the logic to all staff

Now that we've got the grouping working for one staff member, we'll have to apply this logic to our list of all staff members. In order to do this, we would need to group up our original list of salespeople, and apply the above logic as a custom function, as seen below. Note how the initial data preview for Natasha groups all her rows, but after applying the custom function we get the same islands as we got when we did this manually above.

 We can then re-expand our table column to get the full list of islands for all our staff members. There’s also some clean up after that to restore the date types to the new columns.

Now that we’ve got each of our separate islands, we can add a column that computes the number of days for each streak. From this, we see that Natasha did indeed have the longest streak of 4 days.

Custom function to return all islands

If you want to apply all of this in a single step, here’s a custom function I’ve called fn_Date_Islands() that you can use to identify islands among a group of dates. 

The function contains the logic both for working out the islands for an individual salesperson (or more generally, an individual category), and for extending this to all salespeople.

I've also generalised it so that you can specify the name of the Category against which islands may exist (i.e. Salesperson in this example), the name of the Date column, and the name of the output columns for Start Date and End Date…

 We can take our original dataset and apply this function to get our island groupings in a single step…

How about identifying gaps in dates?

Say that instead of working out who had the longest 'hot' streak, we want to work out who had the longest cold streak. We can extend this approach to work out gaps as well. Here's the steps involved in the logic…

1. Remove duplicates and sort.

2. Add an Index column starting at 0.

3. Add a second index starting at 1, which I'll call Next Index.

4. Merge the query to itself, using the join condition [Next Index] = [Index]. Then expand the join result and call the resulting column Next Date. You'll see that this reproduces the dates list, but shifts it one row up.

5. Add a custom column called Gap Start, which adds one day to the original Date column.

6. Add a custom column called Gap End, which subtracts one day from Next Date.

7. The pairs of Gap Start and Gap End values will contain Gaps that appear to go backwards in time. These false gaps appear for rows that are part of an island, so we want to remove these rows. Do this by filtering rows with the filter condition ([Gap Start] <> [Next Date] and [Gap End] <> null).

8. Remove the intermediary columns to give us our final result. 

As before, we can wrap all this into a function and apply it to each staff member. Here’s what we get when we do so…

Note that in Chris's case, he only has a single streak so there are no gaps returned. These null rows can simply be filtered out.

Custom function to return all islands

Here's the full function that returns all gaps for all staff in one step, which I’ve called fn_Date_Gaps(). As with the islands function, you can define the names of the date column, outputted start date column and outputted end date column names as optional parameters.

 When we apply the function against our original dataset, we get all the gaps. Note that the function filters out all categories/salespeople that have no gaps.

Summary

Knowing how to deal with Gaps and Islands is a good tool to have in your data analysis toolkit.

There are ways to accomplish this with SQL as well, and in cases where you can push the logic upstream to SQL, I find it best to do so. But in cases where you don’t have that luxury, like when your data comes from csv files, then you can use the above functions to solve it with Power Query.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

The logic behind creating the date list

I was recently working on a report where I needed to add a slicer that filtered values down to different school holiday periods. To achieve this, I needed a list of all the dates where school holidays fell, and also the name of the school holiday period (so that they could be distinguished).

Instead of the full date list, online sources typically report on the start and end dates for school holidays. Here’s what such a list of holidays may look like:

To get this into a complete list of all school holiday dates, I followed an approach described by Miguel Escobar which I’ll summarise from his post below:

1. Change the data type for the two date columns from Dates to Integers. This makes the next step simple to implement.

2. Add a new column that contains the list of integers between the two columns. Power Query has an easy syntax to create this sequence of numbers…

3. Expand the new Date list column, then convert the data type of the column from Integer to Date.

4. Delete the original Start Date and End Date columns, giving us our final result.

Pushing the logic into a custom function

The approach above works well to create the date range. But as I continued to develop the report, I had more requests come in to include additional date ranges to slice on, like promotional periods and COVID lockdown periods. In keeping with the ‘Don’t Repeat Yourself’ principle, at this point I decided to translate the logic above into a single function to be used as a step in each query. Here’s the function that I built…

Here are a few considerations that I baked into the function definition, which are good to bear in mind whenever you define your own custom functions.

• I made sure to document the function, both with comments in the M code to explain steps and by explicitly defining documentation. If you aren’t in the practice of documenting your custom functions, Lars Schreiber has a good blog series on how to go about it.

• I provided optional parameters so that you can identify which columns to use as your Start Date and End Date, for when the names in your query vary from these generic names.

• While I was thinking about different column names, I also added the option to define what the output name for the final Date column would be, if you don’t want to generically call it Date.

• I also added a step to remove errors from your input date columns if they happen to contain them. Depending on why the errors occured, it may be more useful to replace the errors before applying the function. Adding in the error-removing logic ensures that the function doesn’t break if errors happen to occur.

If you find yourself in a situation where you have to expand a date range into a full list, feel free to give this one a go!

Adding a custom tooltip

I wanted to add a custom tooltip, to allow users to dig even deeper. I was aiming to show how these values broke down across different product categories.

The simplest implementation is to copy this same matrix into a custom tooltip page, and replace the months on the rows with the product category. In doing this, you can see the category breakdown, but only for the calculation item that you’re currently hovering over, as highlighted below.

Note how the total value in the tooltip matches the value that we hover over in the main matrix. The tooltip calculates the correct values, but unfortunately filters the values down to just a single calculation item at a time.

Showing multiple calculation items at once

If we want to show all of the calculation items, we can create a measure that directly applies a given calculation group. For example, we can define this measure to return the Total Sales in the previous year, using the logic in the PY calculation item.

The FORMAT part is important to make sure the measure doesn’t inherit any format strings from the calculation items in the matrix.

The same can be done for our current year sales.

With these defined, we can remove the calculation group for the columns of the matrix in our tooltip. Then we can add our measure for the current and previous year sales. This will give us the correct values when we hover over our current year sales column…

But we get some strange results when we inspect the other columns…

In particular, when we hover over YOY, the tooltip is telling us that there were no sales last year! Something’s definitely gone wrong here.

What’s the problem? Filter context (as usual)

The reason we’re getting the wrong calculation is that the tooltip is generating it’s results based on the values made available to it in the current filter context.

When we hover over YOY, the columns in the tooltip have two calculation items applied.

• For Total Sales CY, it has both the Current item (in the tooltip) and the YOY item (from the main matrix) applied. But since Current is just defined as SELECTEDMEASURE(), the net result is to return the YOY values in the Total Sales CY column.

• For Total Sales PY, it has values for the previous year (in the tooltip) inserted as the current year values when evaluating the YOY item (from the main matrix). Looking at the calculation item definitions ¹ , you can substitute the definition for PY into YOY whenever SELECTEDMEASURE() appears. This will show you why the result ends up as zero for every value.

How can we solve this?

To get this working as intended, we need a way to remove the filter context of the calculation group in the main matrix. I.e. when we hover over YOY, the YOY calculation item shouldn’t be passed through to the tooltip as a filter.

If all this talk of removing filter contexts made you think of applying an ALL function, you’ve earned the right to be quite chuffed with yourself :)

To negate the effect of the main Time Intelligence calculation group, we have to use the ALL filter from within a second calculation group. We can use this new group to toggle the effect of the first group on and off.

This simpler group, which I’ve called ‘Disable Time Intelligence’[State], has a single calculation item named “Disabled”…

For this to work correctly in your tooltip, you need to do two things…

1. Ensure that this calculation group has a higher precedence than the orginal Time Intelligence group. This means that this calculation item will get applied by the Vertipaq engine before the time intelligence calculation item in the matrix. So even though the tooltip may be hovering over a matrix cell in the PY column, the ALL statement over the calculation group overrules the PY calculation.

2. Add ‘Disable Time Intelligence’[State] group to the page-level filters for the tooltip, and select “Disabled” as below.

Once you’ve set these up, here’s what the tooltip looks like in action. When the same month is selected on the matrix rows, the tooltip always returns the same calculations no matter which matrix row is selected.

Wait, how does this work exactly?

Why does ‘Disable Time Intelligence’ apply to the main matrix, but not to the tooltip?

This is because the ALL statement allows us to ignore any filters applied to the Time Intelligence calculation group. So the main matrix has the selected calculation item filtered out.

But then the measures in the tooltip page (Total Sales CY and Total Sales PY) use CALCULATE to reapply the calculation items, after having removed them with the ALL statement in the “Disabled” calculation item.

What about drillthrough pages?

Fortunately, this logic applies equally well to drillthrough pages. If we duplicate our tooltip page and make it a drillthrough page instead (drilling down on the Total Sales measure), we get the same result…

Summary

Calculation groups can be confusing at times, espacially when you start trying to apply multiple groups or items at once. Like most instances when DAX calculations don’t return the expected values, one of your best tools to solve the problem is to carefully consider what the filter context for the calculation is. Once you have that figured out, solving the problem can be much easier - it certainly was for me when I tackled this problem!

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

1. Here are the full definitions for the 'Time Intelligence' calculation group and calculation items used in this example. ↩

---------------------------------------------------------
-- Calculation Group: 'Time Intelligence'[Calculation]
-- Precedence: 1
---------------------------------------------------------

-- Calculation Item: Current
--   Format String = "$#,##0"
SELECTEDMEASURE ()

-- Calculation Item: PY
--   Format String = "$#,##0"
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Calendar[DateKey] ) )

-- Calculation Item: YOY
--   Format String = "$#,##0"
VAR CurrentMeasure =
    SELECTEDMEASURE ()
VAR PriorMeasure =
    CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Calculation] = "PY" )
RETURN
    IF (
        CurrentMeasure = BLANK ()
            || PriorMeasure = BLANK (),
        BLANK (),
        CurrentMeasure - PriorMeasure
    )

-- Calculation Item: YOY%
--   Format String = "0.00%"
DIVIDE (
    CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Calculation] = "YOY" ),
    CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Calculation] = "PY" )
)

My intention with this article is not to go through each technique in a huge amount of detail, but to give you an overview of each approach so you can link to other sources that cover the details. This post also focuses on applications of Calculations Groups, rather than how to set them up in the first place. If you’re not familiar with how to define calculation groups in Power BI using Tabular Editor, check out this video from Guy in a Cube to learn how.

Alright, now after those disclaimers, let’s dig into our list of 10 awesome applications of calculation groups, starting with the most obvious…

1. Time Intelligence

This is the standard application of calculation groups. If you have a Sales measure, you may want to implement time intelligence to see how your Sales stack up as far as week to date, year to date, month on month change, same period last year, etc. Defining each of these calculations for Sales is fine, but if you want to also perform these calculations against your Margins or your Cost of Goods Sold, you’ll end up with way more measures that become harder and harder to maintain.

The alternative is to create a calculation group and define these calculations once. Each of the time intelligence calculations can then be accomplished by filtering your visuals to use only one calculation item. You can also slice between them, or show them all together at once (see below). SQLBI have a great article that introduces you to calculation groups for time intelligence, check it out here.

2. Switch measures

A switch measure is a tried and true technique for changing what measure is represented in a visual using a slicer. This is typically achieved with a disconnected table and a measure that references other measures using the SWITCH function.

Be aware though that a limitation of a conventional switch measure is around how the measure is formatted. By default, a switch measure may refer to measures with different data types. Instead of dynamically updating the data type, a standard switch measure will inherit a Text type, which limits your ability to aggregate values and easily display them in the manner that you want (i.e. with $ and % signs where relevant).

Calculation groups offer a more robust alternate approach for switching between measures being displayed. Using the custom format strings that can be defined against each calculation item, you can set a measure to display as intended. Matt Allington has an article that takes you through this approach in detail. Here’s a preview of what Matt’s finished product ends up looking like…

The trick to implementing switch measures with calculation groups is to create an arbitrary dummy measure ¹ to use in your visuals. You can then define each of your measures that you want to slice between as calculation items. Now when you use the calculation group as your slicer, the dummy measure will be replaced by your selected measure instead.

3. Dynamic format strings

The ability to define a custom format string for each calculation item is very powerful. Beyond using them to implement switch measures, you can also use them to change between different units of measurement.

For example, if you’re dealing with converting metric to imperial values, SQLBI have a tutorial on converting pounds and ounces to grams dynamically with format strings.

A more common use case in Business Intelligence is to convert between currencies. Bas Dohmen (at How to Power BI) has a cool video on how you can use format strings to display the correct currency symbol for the selected country in a matrix…

4. Extend category breakdowns with custom measures

You can extend the switch measure approach above to analyse a category breakdown and provide even more insights. For example, you may have a matrix that shows product spend broken down by year. Besides spend per year, you may also want to include some calculations in the same matrix that compare sales between years, as in the example below.

Matt Allington covers this application in his post Building a Matrix with Asymmetrical Columns and Rows in Power BI.

5. Displaying basic summary statistics

When you have a distribution of values in a matrix, you may want to know some summary statistics about the values - i.e. minimum, maximum, sum and average values. This can help to explore the data quickly, without having to drill down into the full details.

Kane Snyder has an interesting technique for exploring summary statistics for values in a matrix visual. For Margin values broken down by category in a matrix, he has a tooltip that displays what the minimum, maximum and average margin was over the last 6 months.

6. Changing which relationships are active

It can be quite common to have multiple different dates marked against a fact table record, e.g. Order Date and Delivery Date. If you want to relate your fact table to a date dimension, you can create a relationship for each of Order and Delivery Date, but you can only have one active at any given time. To use the date table against both dates, you could duplicate the date dimension and use it as a role playing dimension. But a more succinct alternative is to use the USERELATIONSHIP function to change the active relationship in a CALCULATE statement. Calculation groups can allow you to change the active relationship dynamically with a slicer. SQLBI have a video tutorial on this use case…

7. Activating bidirectional filters

Besides changing the active relationship, you can also change the filtering direction of a relationship. Including bidirectional relationships by default in your model can help with some calculations, but they can introduce ambiguity in your model, and increase your measure evaluation time as unnecessary filtering occurs.

Ideally you would want a way to only activate a bidirectional relationship when you need it. The CROSSFILTER function helps you do this, and by incorporating it into a calculation group, you can change the filtering direction dynamically with a slicer. Erik Svensen has an article that walks you through this topic - Using Calculation Groups in #PowerBI to implement a Many 2 Many (M2M) filter.

Beyond this many-to-many approach, you could also use CROSSFILTER as Erik has done to deactivate a relationship entirely.

8. Controlling bars, lines and labels in charts

You can also give users the ability to control what chart elements are displayed. Using an analogous approach to switch measures, you can use a slicer to change the measures that are shown in the columns and line values for a combination chart. But you can customise charts even more, like letting users control what chart labels appear, since showing them for both columns and lines can make your chart somewhat cluttered. Here’s what Kane Snyder has set up for a combo chart, which he has blogged about and described on a live stream with Reid Havens.

9. Highlight different subsets of data in charts

Another cool technique is to highlight values in a chart, like the minimum and maximum values. This can already be achieved with a switch measure, but calculation groups allow you to dynamically control the labels as well. The labels can be set to only show the highlighted values, and even add some commentary to them as well. Here’s the kind of charts you can build with the approach, which you can read about in Kane Snyder’s post on Dynamic Chart Labels with Calculation Groups.

Besides the labels above, you can set up your visuals to highlight values above the threshold, or your top N values. If you want to learn how to identify and highlight these values, check out my blog post on directing attention with conditional formatting.

10. Slice on multiple columns without unpivoting

In Power BI, you want to aim to have the right table structure from the start. If for whatever reason you can’t set up your tables as needed, you can use calculation groups to fill the gap instead.

For example, say you have a table containing costs over separate categories like this…

If you wanted to slice a chart showing costs to only show the Mining Costs, it wouldn't be straightforward to set up with this table structure.

Unpivoting is the obvious solution here, so you could also load an unpivoted version of the table ² . Including two separate tables with the same data at different grains may make separate sets of calculations easier, but can have unintended side effects - If I were an end user, how would I know which table to build my visuals off of?

An alternate solution to this is to keep the one fact table, and use calculation groups to accomplish the same outcome as unpivoting would. You can define separate calculation items that aggregate over each cost category, and use the calculation group as a slicer to control which costs are shown. Mudassir Ali at Enterprise DNA walks though this approach below…

Summary

These are just some of the uses for calculation groups that have been uncovered so far. Have there been any other useful applications I’ve missed? Let me know in the comments below, and in future we may be able to assemble a follow up list with even more ways to implement this great feature!

Full list of references

• Guy in a Cube - REDUCE the # of measures with Calculation Groups In Power BI

• SQLBI - Introducing Calculation Groups

• SQLBI - Dynamic format strings with calculation groups

• SQLBI - Using calculation groups to switch between dates

• Matt Allington @ Excelerator BI - Dynamic Formatting of Switch Measures

• Matt Allington @ Excelerator BI - Building a Matrix with Asymmetrical Columns and Rows in Power BI

• Bas Dohmen @ How to Power BI - Dynamic NUMBER FORMATTING using CALCULATION GROUPS in Power BI

• Kane Snyder @ Ten Fingers - Combining calculation groups in Power BI to create powerful reports

• Kane Snyder @ Ten Fingers - Allowing users to choose any two different metrics to compare in a single chart with calculation groups.

• Kane Snyder @ Ten Fingers - Dynamic Chart Labels with Calculation Groups

• Havens Consulting - Calculation Groups (with Kane Snyder)

• Erik Svensen - Using Calculation Groups in #PowerBI to implement a Many 2 Many (M2M) filter

• Mudassir Ali @ Enterprise DNA - Avoid Unpivoting Columns With Calculation Groups - DAX Tutorial

1. In Matt's post, he defines his dummy measure as SELECTEDMEASURE(). However, as Kane Snyder kindly pointed out to me, it doesn't matter what you define as your dummy measure since the applied calculation item will overwrite it. ↩

2. In this scenario, I’d have structured the main table as unpivoted in the first place. Excluding the overall cost column is also ideal, since this can be calculated with a measure. It’s still worthwhile being across these techniques though, as you may find yourself in a scenario where it is difficult to shift away from an established data model structure. ↩

So how can you get this to read as the “3rd” highest selling sales, including that ordinal suffix (the ‘rd’ after the 3) on the end? With a bit of DAX, you can get the right suffix showing for these ranked values, and make your Smart Narratives even smarter!

How you can present ordinal suffixes

Besides the above example, I’ve used this kind of technique to add context to a cohort analysis with a custom tooltip page.

In the report screenshot below, I’ve got a matrix visual that shows the first month that a customer made purchases on the rows (i.e. the cohort) and the number of months after their first purchase on the columns. The measure in the matrix shows how many customers from that cohort made a purchase in that month.

When you hover over each cell in the matrix, a custom tooltip appears which generates a smart narrative to explain the cohort’s behavior in more detail. In the tooltip, the highlighted line indicates that we’re considering the column indexed at 4, i.e. the customers 4th month after their first purchase.

If you want to play with this report yourself, you can check it out here.

Using DAX to display suffixes

To build out a measure to include the ordinal suffix, we need a field that returns a list of numbers. To keep things simple, we can generate this list using a calculated table.

Now we can define a measure that takes each number in the current context and works out what suffix to use. The measure will then append the suffix to the number using the FORMAT function. He’s what the full measure looks like.

Here’s how the measure looks when we apply it against our values in a table visual.

Working out which suffix to use

The Suffix variable is what ensures we display the right suffix. The standard rule is that “if a number ends in a 1, then the suffix should be ‘st’, as in ‘first’ “, and similarly for 2nd and 3rd. But the exception to this rule is for 11, 12 and 13, which should read as 11th, 12th and 13th, respectively. So the variable checks the selected value for this exception, then it checks for the standard 1-2-3 rule, and if it doesn’t need to apply either of those it will return the standard ‘th’ suffix.

Allowing for aggregations

Note here that I’ve chosen to use a SUM aggregation in my measure, rather than using SELECTEDVALUE. This is intentional, since we may want to do some calculations and arrive at a new number before finding and applying the suffix. You can see the effect of this in the totals row of the visual. The sum of all the values in the table is computed as 20100, and then the logic in the measure determines that the appropriate suffix for this measure is ‘th’, so it presents the final output as a string that reads “20100th”.

Try it out for yourself!

You can take the exact same logic and apply it to any of your own calculations. All you have to do is replace the references to SUM(‘Table’[Value]) with your own measure.

1.​ Remove columns that you don’t need

Okay, this one should be obvious — more columns means more data. There are columns that you’ll absolutely need in your model, but often times there are some that aren’t actually being used at all. I often find that I have unneeded columns in my reports for one of two reasons: either I’m too lazy to remove them (I’m only human), or because I anticipate that I might need it later. It can certainly be tempting to have a full sandbox with all the fields you could possibly need. But I’ve found that whenever I’ve built a sandbox in the past, the complexity of the report becomes a real barrier for making progress.

My preference has become to start building a model with only the bare minimum number of columns required, and only bring in new columns as they’re needed. Every time I add a new column in, I have the opportunity to reflect on whether or not the data is still appropriately modelled for the scenario at hand. As your stakeholders ask for more report features, you may need to remodel to satisfy their requirements, and this practice helps to identify that need earlier on in development.

If you want to easily work out which fields can be removed without breaking your report, you can use tools like this Power BI Field Finder report developed by Stephanie Bruno.

2. Remove or replace columns with high cardinality

Even if you are using a column in your report, it may be worthwhile to remove or replace it. Columns with high cardinality (i.e. they have many unique values) are often responsible for a bloated model, and this is a result of how the Vertipaq engine inside Power BI works. Vertipaq compresses the data model in a column oriented manner, which means that columns with fewer unique values can be easily compressed, but columns with many unique values cannot.

You can inspect how much space each column takes up in the model using Vertipaq Analyzer, which is an embedded feature in DAX Studio. This feature can help you easily identify which columns may be ideal to delete from the model to reduce the file size. For example, the model analysed below has over 80% of it’s file size taken up by an Invoice Line ID column in an Invoice table at line item level. Since this surrogate key is only in a model as a carry-over from the source system, we can reduce our model size by 80% by removing this single column!

If you’ve never used Vertipaq Analyzer before, SQLBI have a video showing how to Read Performance Analyzer metrics in DAX Studio. ¹

However, if you can’t remove high cardinality columns, you may be able to split them into separate columns that give more compression than the sum of their parts. Datetime columns are especially useful to decompose into separate columns for the date and time. ² But besides splitting the columns, in some scenarios you may prefer to round your datetimes to the nearest minute or hour; I’ve previously written about how you can do this with Power Query.

The technique of splitting high cardinality columns isn’t limited to datetime columns. Gilbert Quevauvilliers has previously written about a novel approach of reducing data model size by splitting a column of decimals into two columns: one for the digits before the decimal point, and one for the digits after. Calculations can then be performed with custom measures that stitch the decimal values back together.

3. Remove rows that you don’t need

Another obvious one. It can be tempting to include a complete dataset with a large date range and every category so users can slice and dice to their heart’s content. But in reality, when decision makers are referring to your reports, they don’t always need to see data before a few years ago. The business context may have significantly changed since then, and including these data may actually tell an inaccurate story if they don’t have that context. ³

If you can’t remove certain date ranges, then you can often omit particular categories, like irrelevant regions or product types. If they’re outside the scope of your analysis, then including these records on the off-chance that your stakeholders might want to report on them may not be worthwhile if report performance is becoming an issue.

You also have the option of aggregating up to a different grain. For example, including hundreds of millions of rows of sales transactions may not be as performant as rolling this up to daily snapshots by product category or store.

4. Turn off Auto Date/Time

One of the things that Power BI does by default in the background is it will automatically generate date dimension tables. These tables define the hierarchy that gets created when you use date fields in visuals, and also help to create time intelligence calculations. In practice, you can usually accomplish all the same things yourself with your own date dimension and time intelligence measures.

A major issue with including these is that a new table is automatically generated for each date column in your model. Each table will contain a row for each of the dates between the earliest and latest dates in the corresponding date field. So if your data warehouse has slowly changing dimensions that use nominal dates like ‘31/12/9999’ to mark the record end date, then not accounting for the Auto Date/Time functionality can lead to large date tables generated in the background. Guy In A Cube have a great video on the impacts of these tables, and how you can reduce a report in size by over a gigabyte (yes, a gigabyte!) just by disabling Auto Date/Time.

To turn off this feature, simply go to File > Options and Settings > Options. You can turn off Auto Date/Time under Data Load, and have this apply either just to the current report file, or globally to all newly created reports.

5. Create extra columns in Power Query over DAX

As you’re loading queries into your model, the Vertipaq engine determines the optimal partitioning scheme to minimise data model size. However, this optimisation step is performed based only on the columns loaded from Power Query, and not based on any Calculated Columns created in DAX. If the partitioning was determined with these columns in mind, then Vertipaq may be able to compress the full table further, but the engine only performs this step once (before considering any DAX calculations). This means that the partitioning scheme selected by the engine may no longer be the best one, and it can mean that the model takes up more space than it would otherwise.

For this reason, you should create additional columns for your model in Power Query rather than DAX, to ensure that you’re getting as much compression of your data as possible. There are practical exceptions to this rule, such as when your calculations rely on relationships, or when the time to reload your data from Power Query is unreasonably slow compared to adding a single new column in DAX. But in general, your aim should be to push the calculations upstream where you can.

Summary

If you’ve got a large data model, you’ve got a number of options to optimise the model size. You can try each of these and see which is most effective. I’ve found that as I’ve gotten more practice with optimising models, the more quickly I’ve become able to identify which approach will work best. I’ve also found that this practice teaches you to keep these considerations in mind as you build your model, so by learning these best practices you can avoid the need to optimise you model in the first place.

PS: If you’re stumped on where to start with optimisations, Michael Kolvasky at Microsoft has recently released a set of best practice rules for your Power BI model. These rules can be loaded into Tabular Editor, allowing it to scan your model and point out various suggestions to optimise your model, even beyond the approaches we’ve discussed above. Check out this video from Guy in a Cube to see how to use the Best Practice Analyzer in Tabular Editor.

1. In fact, SQLBI have a free online course on using DAX Studio and Vertipaq Analyzer which you should definitely check out if these tools aren’t already part of your Power BI toolbox. ↩

2. Since one year’s worth of datetime values can give you over 31 million distinct values, storing this as datetimes can result in high cardinality. When that same year's worth of values are split into separate date and time columns, you have a total of about 87,000 distinct values across both columns, so they compress much better.  ↩

3. Besides, storing all historical data is a job for a data warehouse, not for an individual report. ↩

A common scenario for experiencing high cardinality columns is when you’re dealing with event data, where you have precise datetimes down to the second for a series of events. In a single year alone, you may have as many as 31,622,400 unique datetime values (this is the number of seconds in a leap-year), so your cardinalities can easily come an issue for large datasets.

A useful technique for dealing with this is to store the dates and times in separate columns in your model, which reduces you down to 86,766 unique values for a leap year (number of seconds in a day plus number of days in a leap-year). But this won’t be helpful for every scenario. For example, many of the publicly-available custom visuals oriented around process mining require that you supply a datetime field. In these scenarios, I’ve found it best to aggregate your timestamps by rounding to the nearest minute. For many applications, knowing the precise second that an event occurs isn’t as important as knowing what minute or hour it occurred in. Rounding your datetimes where possible can help Vertipaq to compress your model much better.

Rounding to the nearest minute

If you have a report where you could sacrifice a small amount of precision for big improvements in report file size, then you may find the following Power Query function helpful. This function, which I call RoundDateTime in my reports, takes a datetime column and rounds it to the nearest minute…

To use this function, you just need to add a single step to your query…

The step has two nested transformations within it. The inner transformation applies the custom function, with some extra logic to avoid rows with nulls returning errors. The result is wrapped in a second function that restores the datetime type, which is stripped away when applying the custom function. The two transformations are applied as a single step to make it easier to add to an M query than two separate steps.

Here’s what the function looks like when applied to a column of datetimes…

Rounding to other timeframes

If rounding to the nearest minute doesn’t reduce your model size enough, then you can use this function to round datetimes even further. By adjusting the NearestMinute parameter, you can round datetimes to the nearest 5 minutes, nearest 15 minutes or nearest hour. For example, to implement rounding to the nearest hour, you’d simply adjust the step where you apply the rounding function to use the expression RoundDateTime(_,60) instead of RoundDateTime(_).

This function can also be adapted to work on time columns rather than just datetimes. To do so, you’d just need to change any references to the datetime type in the function definition to refer to the time type instead.

Our challenge

Here we have an event log which describes a compensation request process for an airline ¹ . Each row represents an activity for a particular case, with the time of the activity and the airline staff member undertaking the activity also recorded.

Using the timestamp for each case, we can trace the history of activities that each request moves through. For example, the first case has it's request registered and examined, then once the airline ticket is checked, a decision is made to reject the request.

If we want to understand this process in more detail, we may want to know how long each request spent waiting to complete this activity since the previous one was performed. In a simple linear process, this corresponds to the time spent in each state, where the states might be called something like 'waiting on ticket check', 'waiting on decision', etc ² .

To determine the time between activities, we will tackle this in three steps by creating three calculated columns...

1. Create an index that orders the events in time for each case.

2. Use this index to determine the timestamp for the previous event in the process.

3. Calculate the time between these two timestamps, represented as either hours elapsed or business hours elapsed.

Adding an Activity Index

For this column, we will use the RANKX function to define the order in which each activity occurs. This will be done separately for each case, so that the first activity for each case (‘register request’) will always be indexed as 1.

And here’s what our result looks like…

Adding Timestamp of previous Activity

For this column, we will create a lookup to the previous activity by referencing the previous index for each case.

Note that we have a blank timestamp for the first activity in each case, since we’re not concerned how long the customer took to register their request.

Adding Hours to complete Activity

For this column, we’ll report the hours between activities, since that is a suitable time scale for this event log. Different types of processes will have better-suited timescales, e.g. tracking a user’s clicks on a website is best described in seconds, whereas milestones in a large project are better described in days or weeks.

We’ll also ensure that we track hours as a decimal rather than an integer, so that if we aggregate this value then we will get more accurate results. We can accomplish this by calculating the time difference in units of seconds, then converting to hours.

Representing as Business Hours to complete Activity

In a real world scenario, you may not be concerned about the hours spent waiting to complete an activity if those hours are outside business hours. To give a clearer picture of the process, we can adapt an established technique for Calculating Business Hours Using DAX, as written about previously by Matt Allington. Our event log has events happened between 9AM and 5PM, 7 days a week, so we’ll include weekends as Matt has done in his example (we will also not remove public holidays for simplicity).

Final considerations

Breaking the steps into separate columns as we have done can help us understand the logic behind the calculation. In practice, you’ll want to consolidate the logic into a single calculated column rather than three, as this avoids storing unneccessary fields ³ . This can be accomplished by replacing the first two calculated columns with variables in the definition of the total hours column.

Once we have the business hours to complete each activity, we can visualise this to see where our process faces the most delays.

Upon inspection, I wouldn’t consider it particularly surprising that the activity of ultimately paying compensation to customers is the longest step for a reimbursement process. But there are other insights to note here as well — for example, the duration of the ‘examine casually’ and ‘examine thoroughly’ appear to take about the same amount of time on average. In reality, the ‘examine casually’ activity is undertaken for all cases, but sometimes they are then examined more thoroughly. This is a symptom of the process actually not being a linear one, as different cases may follow a different chain of activities. Techniques have been developed in the field of process mining to address such scenarios, and I imagine they’ll form the topic of future blog posts.

1. The dataset is as described in the book Process Mining: Data Science in Action by Wil van der Aalst, and can be downloaded here. ↩

2. Note that in more complicated processes with branching paths that a process can follow, tracking states may not be so simple. But for the purpose of this article, the assumption of a simple linear process will do just fine. ↩

3. Creating unneeded columns isn’t best practice with Power BI, especially when one of the fields is a datetime. Datetime fields often have a big impact on your file size, as the high cardinality makes it hard for the Vertipaq engine to compress them. ↩

In this post, I'll take you through a technique that I've used to build a histogram visual even when you don't have the ability to alter the data model used by your report. The situation described is admittedly quite niche, so I would recommend that you treat this article as a learning resource to show you some creative ways to apply DAX to sidestep modelling constraints, rather than expecting to immediately find a practical application of the technique in your own reports.

Our challenge

As a reminder, last time we created a histogram describing a retail chain’s sales performance. Our histogram showed the count of stores by how many months they sold under target. We built our final histogram using a What If parameter for the axis values, and a custom measure to count the number of stores underperforming for each of the possible months. Our final solution ended up looking like this…

If you’re working off of an Analysis Services Tabular model, this method proves to be ineffective due to the inability to add What If parameters. However, we don’t fundamentally need a What If parameter for this purpose, we just need a field that has the values 0 through 12. As long as there is such a field in our model, we can use that field as the axis of our histogram. Even though there may be relationships in place which would filter the calculated values, we can still write a measure for our histogram which can ignore this filtering.

Our situation

For this example, we’ll work off of a dataset made in Power BI rather than a Tabular model — this will give us better visibility on what’s going on under the hood. Despite this, the approach we’ll take works equally well on a Tabular model, when it comes time to implement it yourself.

Just as in the last post, we've got a data table with sales performance data by month and store. This time around, I've added a date dimension, which is related to our main data table on the month column (which is a date type field).

Here are some of the fields in Dim Date…

At first glance, it would seem as though a simple integer field like Day or Month may be suitable to use for the histogram axis. However, recall that our histogram varied between 0 and 12, so we’d be missing the 0 value by using either of those fields. Instead we can use the Relative Day field, which is used to denote how many days it is between the date in the current row and today (or more accurately, the last refresh date). Your own models may not have such a field, so you may have to do your own analysis of the fields in your model to find a suitable one.

Note that in the context of an Analysis Services connection, you won't have the ability to see the underlying tables to find a suitable field. In this case, you can explore the values in the tables by either dragging them onto the report page canvas to see the distinct values as a table, or you can use DAX Studio to connect to the model and query the full table. If you haven't used DAX Studio for this before, Matt Allington has a great blog post to teach you the basics.

Now that we have identified the field to use for our axis, we can define the corresponding measure for the histogram columns. We can start by defining the measure as we did in the last post, but this time with Relative Day as the selected field instead. For those who need a refresher, this measure creates a virtual table of the total number of months where sales were below target by store. It then counts how many stores underperformed for the selected number of months. Here’s how our measure is defined…

Now we can drag Relative Day into the chart axis well, and Store Count into the values well, giving us the following…

Okay, this is admittedly looking a bit odd. The first thing we’ll need to do to make this return something more sensible is to limit the Relative Day values on the axis just to our 0 through 12 values.

After limiting our axis values, we find that the measure returns 0 for all columns, rather than a distribution of values. What’s going on here?! Well, we’ll have to take a brief step into DAX theory land before we can tackle this problem.

A quick lesson in how DAX measures work

To get around this, we need to understand how measures get evaluated by the Vertipaq engine, which is the brains behind DAX. Before the engine does any arithmetic to count our rows, it follows a sequence of steps to filter the data model…

1. First, it will consider all of the filters being applied to the visual element. E.g. for a cell in a pivot table. it will consider the selected column and row, and any external filters applied.

2. Then, it will modify those filters according to any applied filter context modifiers, like CALCULATE or CALCULATETABLE.

3. Next, it will then apply those filters to the data model.

4. And finally, it will follow any active relationships to further filter the rows in the model.

Only at that point will it perform the arithmetic operations to give us our measure value.

If that sounded confusing, that's probably because it is! You don’t necessarily have to understand all that the first time you read it, as understanding how DAX expressions are evaluated can be somewhat challenging. But it is one of the challenges in learning Power BI with the biggest payoff, as it will make many of the problems you face in developing reports so much easier to solve. You can learn more about how DAX expressions get evaluated in Rob Collie and Avi Singh's incredibly digestible book Power Pivot and Power BI (look at Chapter 7 in particular).

Back to our scenario…

With that extra context, we can figure out what’s going on with our measure. The evaluation step that causes us problems is in following the active relationships. When we are looking at the histogram column equal to 1, recall that this is actually referring to the values where relative day equals 1. Since this is one day in the future, and all our sales performance data is historical, this filters the model down to exclude all our sales data. In other words, we’ve reduced our row count to zero before we even have a chance to evaluate the measure!

So how can we get around this problem? Well, we could delete the relationship, but then we wouldn’t be able to filter using other date fields. Instead, we can remove the filtering effect from Relative Day by using a DAX function that is (fittingly enough) called REMOVEFILTERS. We’ll need to use this wherever filtering would occur when the measure gets evaluated — this ensures that the filtering via the relationship will be ignored. In our case, the filtering occurs when the StorePerformance variable and the final measure output are evaluated. Here’s what our measure looks like when we add these extra clauses (marked by comments)…

Now when we use this new measure in our histogram, we will get the same histogram as we made in the last post, but this time without relying on any custom tables.

Additional considerations

When building out a report page with this visual, it may be quite natural to want to add a date slicer to your page. This is still an option in this scenario, however you’ll have to use the date field from the fact table in the slicer, rather than the field in your date dimension. Introducing a filter context from the date table here will potentially filter the Relative Day values before the histogram columns can even be evaluated.

Also recall that the last time we built this histogram, we also built a tooltip to list the stores represented in each histogram. The histogram with the tooltip ended up looking like this…

If we want to get this tooltip working for our new histogram as well, we need to modify the measure that indicates whether or not to include a given store in the tooltip. We can use REMOVEFILTERS for this again…

Summary

Even in scenarios where you aren't able to alter your data model, you can often still find ways to solve problems that would conventionally involve new tables, columns or relationships. In this case, we were able to use an established field in our model, and alter the evaluation context of our measures in a few places to adapt the field into a What If parameter.

I will caution that having to write complicated DAX expressions can often be a warning sign of the need to remodel your dataset. But even star schemas can’t address every scenario; they are built to cater to generalised situations. When you’re working off of a static model meant to service many reports or users, that general scenario may not cut it for you. As you get more familiar with DAX, you can pull off these kinds of tricks to sidestep the constraints of working with static data models.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

What is a histogram again? Isn't that just a column chart?

Histograms, quite simply, are a type of column chart. For many people, they may seem like one in the same, but while all histograms are column charts, not all column charts are histograms (similar to how all humans are mammals, but not all mammals are human). While you may be used to column charts that display financial measure like profits, histograms are column charts that give you information on the frequency of values, often times using a COUNT measure in Power BI. Histograms have a number of particular features that not all column charts have...

• The x-axis contains continuous values (i.e. numbers) rather than categorical values (i.e. names of groupings), and the y-axis contains frequency / count data.

• The values on the x-axis can be individual values (ages 0, 1, 2, 3, etc.) or binned values (ages 0-9, 10-19, 20-29, etc.). An item represented in a column of a histogram could have any value within that bin. We’ll only focus on histograms using individual integer values, since there are a number of articles already written by others on using binning for segmentation.

• Histograms include bins that have a count of zero. The context of the situation may make it reasonable to omit empty bins at the top or bottom end of the distribution if they are not between two nonempty bins, e.g. you don't need to include ages 0-9 when describing staff at a company.

Using a histogram to answer business questions

Histograms aren’t just useful in statistics — they can also be used effectively in business scenarios to provide useful insights. In this example, we will be answering questions about a hypothetical retail company based in Melbourne, Australia. We have been given data about how each of the company's 16 stores have performed in each month of the 2019-2020 financial year. This dataset contain the Sales and Sales Targets, with an underlying table that looks like this…

Since the table contains a snapshot of the sales up to the end of each month, let's call this table Fact Sales Snapshot. Note here that the Month column is a date type field, but I've changed the formatting to make it more readable.

Based on the data in Fact Sales Snapshot, we've been asked to answer this question:

For how many months did each store sell below their sales targets?

If we want to answer this question, we're faced with a problem. Usually in Power BI, we build visuals by dragging and dropping fields onto the report page to form our axis and values. But if we want number of months to appear on our column chart axis, what field in our dataset would we use to make this axis? It's not immediately clear, since there is no field in our dataset with just integer values for our month counts. To deal with this problem, we're going to build out a table with these month values ourselves. And using this new table, we're going to build a histogram that shows the count of stores against the number of months that they were performing below their target.

We'll be tackling this problem in two different ways. The first approach will be a so-called static solution, which relies on a calculated table which aggregates our existing snapshot table. The second will be a dynamic solution using a What-If parameter and a custom measure instead. And finally we'll extend our dynamic solution with a custom tooltip to tell us which stores are represented in each column of our histogram. As a taster for what’s to come, here’s what our final solution will look like…

Static solution using calculated tables

To being answering this question, we need to calculate the Sales to Target for each store's monthly sales. To do this, we can define a simple DAX measure as follows, and define it as a percentage.

Now using this new measure, we're going to build a new calculated table in DAX, which returns a unique list of stores, and for each store it counts how many months the Sales to Target measure is less than 100%.

The output of our new Store Performance table looks like this...

And we can drag these two fields into a column chart visual to get something like the following…

And here we have our histogram! It certainly answers our business question of how many months each store sold below target. But it has a few shortcomings that means it may not be the best approach for us to apply to other similar situations…

• We've added a new table which will take up more space in our model and increase the file size, especially if we had a lot more stores or were doing this analysis for something like products which would likely have many different unique values.

• We can't slice this table based on other attributes like month or store region.

• For us, we could be content with not showing months 11 and 12, but we may want to give our report audience confidence that there are no stores that performed under target for more than 10 months.

Given all those shortcomings, we're going to tackle this problem again, but this time using a more scalable approach. Our new approach will not only address these issues, but will also allow us to include other features in later sections which will be significant value-adds for describing our business situation.

Dynamic solution using What If parameters and measures

This new solution will rely on building a dynamic measure that we will still be able to slice our data model on. This is a more robust approach that our first attempt, since our aggregated values are calculated as required based on filter context rather than being stored as fixed values in the model. However, this new solution still need to address the problem we identified earlier around needing a field for our histogram axis.

This time around we're going to to make another calculated table, albeit a much simpler one. We’ll define a What If parameter called Histogram Axis, and allow it to take values between 0 and 12.

Note that we’ve deselected ‘Add slicer to this page’, since we won’t be slicing on this value. We also won’t need the Histogram Axis Value measure that is automatically generated, as we’ll be creating a new custom measure instead. We’re simply using the parameter to define a calculated table with the values that should appear on the x-axis of our histograms, which we could have equally well defined manually as a DAX table.

Now that we have our histogram axis, we can define the measure that we’ll use to generate the values for our histogram. In this measure, we will generate our Store Performance table as a virtual table variable, so that it gets evaluated within the measure rather than being stored in the model. We then filter the table down to stores with sales performance corresponding to the currently selected Histogram Axis value. Finally, the measure returns the row count for this filtered table.

We can now use this measure to generate the same histogram as before, but since it is dynamically calculated from the values in Fact Sales Snapshot, we can now we can use slicers on the histogram. E.g. we may want to limit our analysis to stores in a particular region, or just to certain months of the year.

Note that when we use the slicers, our original static solution is not affected.

Formatting our histogram

Next we’ll format our histogram in a conventional manner where there are no gaps between the columns — this acts as a visual cue to enforce the idea that the columns represent continuous values.

To do this, we (ironically) have to change our x-axis from continuous type to categorical. While our data represent a continuous series of numbers, for some unknown reason column chart visuals only allow you to adjust the width between columns when the data on the x-axis are marked as categorical. We can change this setting in the Format pane.

But unfortunately, when we make this change, our column ordering changes! The axis now gets sorted descending based on the measure value, but this is easy for us to correct.

Finally, we can adjust the width between the columns using the Inner padding option in the Format pane.

Adding a tooltip to list stores represented in each column

Now that we have the distribution of monthly store performance, the next thing we'd probably want to know is which stores are the ones performing well and which aren't. An effective way to show this information would be with a tooltip that tells us which stores are represented in each column of our histogram.

We can do this by creating a custom tooltip. To do this we can create a new page in our report, then define the page as a Tooltip page in the Format pane and reduce the page size to something small enough to use as a tooltip.

On our tooltip page, we can build a simple table visual using the Store field in Fact Sales Snapshot. By default, this will show all of our stores, but we only want it to display the stores for the currently selected histogram column. To accomplish this, we'll create another measure, which will be used as a visual-level filter on our visual. For each row in our table of stores, it will determine how many months the store underperformed. It will then compare that to the histogram column that is currently selected in creating the tooltip, and will only include the store if the store's performance matches the histogram column value.

To enable this filter logic, we just have to drag the measure into the visual level filters for our tooltip table, and only include values for which the measure returns 1.

After setting all this up, we can navigate back to our main report page, and format the histogram visual to refer to our tooltip page as a report page tooltip.

And with that, we can now see our dynamic histogram with tooltip in action!

Other scenarios to use histograms in

These techniques of building histograms for analysis can be very powerful when used effectively. With these insights, a report user may be able to identify the stores with poor sales performance and used that to investigate (and hopefully resolve) the key factors driving their underperformance. You could even extend this scenario to analysing sales of individual products and identify stores where that product has underperformed (rather than considering the sales performance across all products in store as we have done). Customers shopping at these stores may offer valuable insights to help your company to improve its product offering.

Summary

In this article, we built a histogram in Power BI which showed us the distribution of store performance, based on how many months each store’s sales were below target. We first made our histogram using an aggregate table in DAX, and then we extended our solution to use a measure and a What If parameter. We then formatted our histogram to remove the space between columns, and added a custom tooltip to show which stores were represented in each histogram column.

As a final note, bear in mind that the approaches we've discussed both rely on you having the ability to define a calculated table in your model. However, if you happen to be working off of a report connected to an Analysis Services model, you probably won't have the same kind of flexibility to create additional tables. In my next blog post, I'll take you through an implementation you can use to create histograms when you can't actually create a new table in your model, so stay tuned!

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

Standard Conditional Formatting for Column Charts

As with other visuals, you can use conditional formatting in your column charts to encode colour based on column height. This technique will draw attention towards the higher values in your chart. You can easily enable this feature from the Data Colors section of the Format pane. Let’s see how we can apply conditional formatting to a chart of sales performance.

For this chart, we’ve format based on a colour scale using the sum of our sales as below.

While the chart does look visually appealing at first, there are a number of reasons that I try to avoid using this feature…

• Discrete colour steps are easier to tell apart than the continuous colour gradient we’ve used. Cognitive psychology tells us that people struggle to accurately tell the difference between more than about 7 different colours at once.

• It’s easy to notice a difference in height between two columns, but harder to tell two different colour hues apart. You can see this for yourself if you look at the last 3 columns of the chart - the heights clearly tell them apart, but could you work out which of them has the highest value based on colour alone?

• Relying on the colour gradient alone can also be deceptive. Looking at the column for March, it has almost the same height as the column for January. However, March looks darker despite being the (slightly) lower value because it has light colours on either side to contrast against. Meanwhile our brains compare January to the darkest column in December, and trick us into thinking January is lighter than it actually is.

Given these issues, there are a few alternate techniques you can use that I find more effective for directing your audiences attention towards high values.

Highlighting the Top N values

With a bit of DAX, we can define a measure to help us conditionally format our chart to emphasize the highest values. To build such a measure, we must first consider how our sales performance data is structured. In this example, we have a table called Fact Sales Snapshot, which shows how each retail store for a company based in Melbourne have performed in each month of the 2019-2020 financial year.

We’ve also got a date dimension in our model, related to our sales data on the Month field.

To give us some flexibility here, we’re going to define a What If parameter so we can vary how many values to highlight. Let’s call this parameter Top N, allowing it to vary between 1 and 12. I’ve also renamed the created measure from it’s default name of Top N Value to simply call it N.

Now we’ll create a measure which will identify whether a given column is within our top N sales months. To create this measure, we need to do three things…

1. Create a virtual table containing the data presented in our chart

2. Filter that table to the top N sales months

3. Check if the month currently selected in our column chart is within those top N sales months

Our measure that does all of this is defined below.

Finally, we can use this measure to define conditional formatting for our column chart. This time around, we’ll format based on rules defined by the values of our new measure…

And now we will have our top N months highlighted in our chart! We can even vary our What If parameter and see the highlighting update in real time…

In some situations, this approach is more effective than standard conditional formatting, since the bolder colours draw attention more definitively than a colour gradient. While it can be very effective when the ranking of columns is important, it isn’t always ideal when values are very similar. For example, if you were highlighting the top 4 sales months above, you wouldn’t pay much attention to the October column as the month with the 5th most sales, even though it has almost the same sales as March, having the 4th highest sales.

Highlighting values above a threshold

An alternate way to highlight vales that doesn’t have this shortcoming is to highlight values above a particular threshold. This is most effective when you have targets that you need to quickly know when you’re operating above or below them.

To demonstrate this approach, we’re going to define another What If parameter, this time called Target. We’ll give it a range between $0 and $4 million, in steps of $10,000. Now we could bring the measure for this parameter into our chart as a line, but let’s see what happens when we do…

Since our What If measure returns a value even for months when there are no sales, it has thrown off our chart axis. To correct this, we can redefine the measure to only return the target value for months with sales.

And now we get an updated chart…

With our updated chart, we can define a measure to flag the columns above our threshold, and set the conditional formatting as we did earlier.

And now our chart will update the highlighted values, depending on where the threshold is set.

If you prefer, you can remove the target line from the visual and the conditional formatting logic will still work all the same.

Summary

Depending on the context that your data exists in, there are multiple approaches you can use to highlight the data that matters. Besides the standard conditional formatting options for charts, you can emphasise the highest ranking values, or highlight any columns above a particular threshold. When you pick the right approach for highlighting values for your situation, then that can make it a lot easier for your audience to work out whether or not they need to take action based on your visual.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

How do slicers usually work? 

The way that slicers work by default is based on filtering data using a logical AND condition. Whenever a visual is generated, the calculation engine in Power BI will consider any filters or slicers applied to the visual, and use those conditions to filter the data model. It will then apply filter context modifiers from any defined CALCULATE statements, and finally it will perform the DAX operation in the measure based on whatever data is remaining after this filtering. 

The default filtering behaviour follows an intersect set operation, where it only considers the rows that satisfy all filter conditions at once. Filtering in this manner works well for many situations, but if instead we want to filter based on rows that satisfy one condition or another (i.e. a union operation), then we need to use an alternative approach. I've seen other blogs go through different approaches to achieve this, but they often involve writing complicated measures that can be hard to interpret. Here, I'll take you through a simple implementation of applying this OR logic, and then we'll generalise this approach to XOR conditions (where we only include rows that satisfy one slicer condition or the other, but not both at once) and to aggregated data. 

Our scenario

The situation we'll consider comes from a Power BI question that I recently answered online. In this scenario, we have a simple table in our model that looks like the following...

For anyone following along at home, the M code to define the table is also below:

Our aim is to have a table visual for these data on our report page, and we want to slice the data based on whether the row contains one of the numbers or one of the letters. The way that one would typically add these slicers is to drag the fields onto the canvas as slicers, and use them to filter the visual. However, this will follow the default filtering behaviour of showing rows satisfying both conditions simultaneously, so we will observe something like this...

In this situation, when we select options on both slicers, we only see the number-letter combinations that satisfy both slicer conditions (i.e. the letter I and number 8). And whenever we make a selection on a slicer, not only does the data table get filtered but the other slicer gets filtered as well. There are straightforward solutions to this issue, such as disabling the interactions between slicers or building the model out as a star schema. But even if we went down one of those paths, our table would still get filtered based on that pesky AND condition.

Adding an OR filter

To achieve OR filtering instead, we have to play a few tricks using some DAX measures and calculated tables. To start with, we'll need to generate two disconnected tables in DAX, which contain all the unique letters and numbers in our main data table...

Note that you can do this in Power Query if you prefer, but it doesn't make a significant difference here.

These new tables will ultimately be used as our slicers, but in this scenario it is important to ensure that these new tables do not have a relationship with the main data table. Conventional filtering would require a relationship between the tables for filters to propagate between them, but remember that we are not doing conventional filtering. 

If we were to drag these new fields onto the canvas as slicers at this stage, we would find that there is no interaction between our slicers and the data table. This should make sense, since there is no relationship between them. In order to apply our desired OR behaviour, we must now write a custom measure as follows:

Once we have this measure, we need to drag it into the visual-level filters for out table visual, and apply the condition of only including rows where the filter equals 1 (i.e. those that satisfy the OR condition). It is important for this to be defined as a measure and not as a calculated column, since the slicers will be changed dynamically and we need our OR logic to also change dynamically. With this filter condition applied, our table visual will now only show rows that follow our desired OR condition.

Extending this to XOR conditions

If you instead wanted to apply an XOR (or exclusive OR) operation, then you can tweak the measure above to satisfy this. We'll just need to add a clause that excludes the rows that satisfy both filter conditions at once. For readability, we'll use variables for the logical filtering conditions in this instance.

Now we can apply this measure as a visual-level filter as before to get the following...

Generalising to aggregated data

This technique works fine when your slicers are at the same grain as the data you are hoping to display. But what about when you want to apply an OR condition to aggregated data? For example, you may want to know the total sales of products that are either sold in Australia or coloured blue. Or in our example, perhaps we want to know the sum of the numbers in rows that fulfil the OR condition, or the first letter alphabetically that fulfils the condition. 

The approaches we used above won't work out-of-the-box if we tried to apply it to a visual with aggregated data. This is because the measures we wrote earlier only work when they are evaluated against a single row of the data table, as indicated by the presence of the SELECTEDVALUE statements in their definitions. To apply our logic more generally, we have to dynamically iterate through the table row by row within a single measure evaluation, and then calculate our final output based on the resulting filtered table.

The FILTER function can be used as our iterator here to return a table with only the rows that fulfil our OR condition. Once we have our table, we can use CALCULATE to determine the aggregated value, considering only the rows in the filtered table. Here is how this implementation looks if we calculate the sum of all numbers from rows fulfilling our OR condition...

We can define a similar measure to determine the first letter alphabetically...

And of course, we could similarly use the XOR condition instead if it was what the situation demanded. Here's what these implementations end up looking like...

Summary

While filtering based on AND conditions is the default in Power BI, you can still easily achieve filtering based on OR and XOR conditions using a few calculated tables, measures and a bit of DAX know-how. Relationships and star schemas are of course still best practice to follow in general, but in specific scenarios you can achieve more by intentionally straying outside of these guidelines.

Try it out for yourself!

Download the sample Power BI report file I used in this post to play around and build your own understanding hands-on.