Category: Power BI

Gerhard Brueckl explains how Power BI can act as a data virtualization engine:

But lets examine what we currently have in Power BI:
– a semantic layer and data modelling capabilities
– access to various data sources via Direct Query (remember, we do not want to load any data!)
– ability to combine data from those sources

The last part is the most important one here and you may wonder what I am talking about. And you are right, by default a DQ model is only linked to one data source at a time but you can add other data sources manually in the Power Query editor! 

This is where it get’s interesting and what this blog post is about.

Read the whole thing.

Marco Russo shows how you can display just the top few products in each grouping using Power BI:

A common approach to this scenario is to create a complex measure that hides the result if the element should not be displayed. In other words, it computes the ranking of the product and it blanks out the result if the ranking is larger than three. Though easy to implement, this approach requires modifying all the measures that should be displayed in a visual and negatively impacts performance. A better solution is to create a specific measure to use in the visual-level filter: that measure is executed only once for each product to define the set of visible items, without requiring any change to the other measures used in the visualization. We will see different options to solve the scenario.

Marco shows off a few techniques to get this done.

Matt Allington shows how you can now use icons as the output of conditional formatting in Power BI:

Note how the icons above have both shape and colour so you can differentiate between them even if you are colour blind.  This is best practice.

You can also change the default formatting to work on the hard coded number settings that you specify. In the example below I have changed the settings to work on absolute numbers instead of percentages (note the changes in the highlighted boxes).  Also note that I have set the minimum and maximum numbers shown as 1 and 2.  To do this, simply delete the value in these boxes.  Thanks to Chris Webb for finally helping me understand how this works.

It’s easy to go overboard with this, but I’m happy to see conditional formatted icons in place; done right, you can pack a lot of information into a small space with them.

Imke Feldmann has a new M function for us:

While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the optional 4th parameter.

Click through for the script and sample uses.

Matt Allington shows how you can build up a Pareto chart in Power BI:

Pareto Analysis is a statistical technique that applies the Pareto Principle to data. This is more commonly known as the 80:20 Rule. The Pareto Principle is based on the presumption that a relatively small number of inputs (20%)  have most impact on the results/output (80%).  The 80:20 rule can be applied to a wide variety of data in most businesses. 

Examples include:
– Which 20% of products make up 80% of sales
– Which 20% of customers make up 80% of profit.

Pareto analysis is a rule-of-thumb technique but it does provide reasonably useful results much of the time.