Implementing error checks in Power Query
Allowing incorrect data to appear in a report can diminish trust in your organisation’s reporting capability. To avoid incorrect data appearing in the first place, we can force refresh errors when certain business rules aren’t met (since usually stale data is better than incorrect data). In this post, I demonstrate a pattern for applying custom error messages when business rules are violated. These messages can be set up to provide error details that cut down troubleshooting time.
Make the transition from Excel to Power BI smoother by emulating PivotTables and PivotCharts
Migrating users from Excel to Power BI can be a challenge. Even users who know their way around a PivotTable may resist the move, despite the similar drag-and-drop experience in Power BI. In this post, I show how you can use Field Parameters to build a visually-similar experience to Excel PivotTables and PivotCharts. This can provide a familiar-looking stepping stone for users who have yet to make the jump over to Power BI for self-service.
Swapping Gantt chart hierarchies with Field Parameters
With the new Field Parameters feature, you can now change the presentation of your visuals in a number of new ways. One way is to change the order of fields shown in a table or matrix, although the standard application of field parameters for this use case doesn't allow you to enforce a sort order. Here's an approach that allows you to retain your sort order, with an application for creating a Gantt chart of different hierarchies with overlapping members.
How to include a legend with a conditionally formatted chart
Conditional formatting is a fairly routine feature to add to a column chart. But Power BI doesn’t allow you to display a legend for each of your conditionally formatted columns by default. In this post, I show you a technique for including a legend to explain what each colour of your conditional formatting corresponds to.
Dealing with Gaps and Islands in Power Query
Gaps and Islands analysis is a useful tool for finding distinct periods in a list of dates, like if you wanted to know when each customer was active or inactive. In this post, I show you a technique for finding gaps and islands in a list of dates against multiple categories, and provide custom M functions that you can use yourself for these kinds of problems.
Convert a date range to a list of dates with Power Query
In this post, I’ll show you how you can take a range of dates (defined by a start date and end date), and convert it into a full list of dates. This technique is by no means new, but we’ll expand this approach by converting the logic into a custom function in Power Query so you can repeat the logic in separate queries.
How to reset calculation groups in a custom tooltip or drillthrough page
Using calculation groups in visuals can make your life a lot easier. But when you try to use custom tooltips or drillthroughs on these visuals, the calculation groups get applied to those as well. This can be challenging when you want your tooltip to show calculation items besides the one you’re hovering over. In this post, I’ll show you how you can address this issue, so you can reset and reapply your calculation groups for tooltips and drillthrough pages.
10 ways you can use Calculation Groups in Power BI
When I first started learning about Calculation Groups, I could certainly see how much potential this new DAX feature would eventually have. The global Power BI community has certainly proven me right, as they’ve discovered plenty of great uses for them! Here are my top 10 use cases for them, spanning time intelligence, dynamic formatting, controlling relationships and enhancing the user experience…
Displaying ordinal numbers in your Power BI Smart Narratives
In using the Smart Narratives feature for Power BI, I’ve found a few tricks to make your narratives even easier to read. For example, say you used Smart Narratives to show the sales performance for a given state. If that state came up 3rd best, how could you get your narrative to describe it as having the “3rd” highest selling sales, including the ‘rd’ after the 3? With a bit of DAX, you can get the right suffix showing for these ranked values, and make your Smart Narratives even smarter!
Top 5 tips to reduce your Power BI data model size
If you’re building Power BI reports on the regular, then you’ve probably built a few reports that are on the large side. My usual rule of thumb is that if a model is getting beyond 100MB in size, then you should probably start thinking about how you can optimise it. To that end, here are 5 tips to ensure that your data model can stay compact while still delivering all the insights you need.
Rounding datetimes to nearest minute or hour in Power Query
One of the most common culprits behind a large data model in Power BI is the presence of columns with a high cardinality. A common scenario where this occurs is when you’re dealing with event data, where you have precise datetimes down to the second for a series of events. For many applications, knowing the precise second that an event occurs isn’t as important as knowing what minute or hour it occurred in. In this post, I’ll introduce a Power Query function you can use to round a datetime field down to the nearest minute, 15 minutes or hour. This can reduce cardinality and lead to a more compact report file size.
Calculating business hours between activities using DAX
Whenever you're working with event data, you often have data on activities performed, and the time at which they were performed. This data is useful for process mining, where you can track durations of cases that follow a process, identify bottlenecks in the process and identify instances where the intended process is not followed. In this article, I'll walk you through how you can track the time between events, so you can note how long something spends in a given state.
How to improvise What If Parameters in Power BI Live Connection models
In my last blog post, I explained a flexible technique you can use to build histograms in Power BI using measures and What If parameters. But one issue with that approach is that it assumes you have the ability to add a new table to your model. Fortunately, for many users this is no longer an issue, thanks to the recent release of DirectQuery over Azure Analysis Services models as a preview feature for Power BI Desktop. However if your reporting data is stored on premises, whether in SQL Server Analysis Services or Power BI Report Server, it may be a while until you can make use of this feature. 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.
How to make Histograms in Power BI to answer business questions
Histograms are commonly used in statistics, but they can also be useful for answering business questions. However it’s not immediately obvious how to set them up in Power BI, so we’re going to go through a few techniques for how to build them to address a business scenario. Our end result will have the flexibility to slice on different values, and will give us a custom tooltip with additional insights.
Top 3 ways to direct attention with conditional formatting for column charts in Power BI
Conditional Formatting can be a powerful tool for giving your audience context, as long as it is used appropriately. In this post, we’re going to discuss 3 ways that you can use colour and conditional formatting in column charts to direct your audience’s towards the data points that matter.
Slicing based on OR and XOR conditions in Power BI
Slicers on a report page in Power BI are an effective way to slice and dice your data under different conditions. But one situation that can be challenging to implement effectively is being able to apply multiple slicers, and show the rows that satisfy one condition or the other, not just the rows that satisfy both. In this blog post, I'll show you a simple implementation to allow you to address this scenario.