Press "Enter" to skip to content

Category: Power BI

Power BI Pareto Charts

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.

Comments closed

When Values Disappear in Power BI

Chris Webb explains what happens when a selected value on a slicer disappears in the underlying data set in Power BI:

There is a slicer on the left with five items in it, a table showing the actual contents of the table (I’ve disabled visual interactions so the slicer doesn’t slice the table) with five rows and a card showing the output of the following measure:

Selected Number = SELECTEDVALUE(MyNumbers[Column1], "Nothing Selected")

In the screenshot above you can see I have selected the value 78 in the slicer and the measure – as you would expect – displays that value.

Now what happens when you refresh the dataset and the table contains a different set of numbers? 

Read on for the full explanation.

Comments closed

Adding Line Breaks on DAX Measures

Gilbert Quevauvilliers shows how you can add line breaks to DAX measures for formatting results in Power BI:

I was at a customer and they were drilling through between pages, and I wanted to put in what the filter selections were when they drilled through to the new page.

This would allow them to easily see what had been on the previous page, and to avoid going backwards and forwards.

I quickly created the DAX measure to show the filters, but one thing that bugged me was that it looked a bit clunky and not clear in terms of what the filters were.

I knew that I wanted to use a Line Break to put each filter on a new line. 

Read on to see how you can do this.

Comments closed

SUMX() in Power BI

Rob Collie explains the power of SUMX() in DAX:

Have you ever written an array formula in Excel?  (Don’t worry, most people haven’t).  Have you ever written a FOR loop in a programming language?  (Again, don’t worry, there’s another question coming).  Have you every repeated something over and over again, slowly building up to a final result?

That’s what SUMX() does.  It loops through a list, performs a calc at each step, and then adds up the results of each step.  That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.

Read on for a few examples.

Comments closed

Scheduled Refresh on Power BI Custom Connectors

Kasper de Jonge shows how to enable scheduled refresh on custom connectors in Power BI:

Here is a post that is long overdue as the functionality has been available for almost a year now. Today we will enable scheduled refresh to the Strava custom connector I build before.

The trick to this is that we need to extend the code of the Strava connector with a “TestConnection” function as is described here. This will allow the Power BI gateway to know how to test the connection.

There’s just a little bit of code involved.

Comments closed

IsNumeric in Power Query

Matthew Roche (with an assist from Imke Feldmann) shows how you can check if a value—or part of a value—is numeric in Power Query:

Using Imke’s approach, this is what I ended up doing:

1. Extract the last two characters from the source column
2. When the extracted characters contain a number, convert them to numeric
3. Standardize the resulting value to represent the correct year

Read on to see how Matthew pulled it off.

Comments closed

Creating a Slicer Plus Histogram in Power BI

Matt Allington shows how you can create a combination slicer and histogram in Power BI:

I was doing some online shopping last week and saw a price slicer with a histogram to show the concentration of price points.  I thought it was pretty cool, so decided to see if I could build this in Power BI.  I came up with a solution that works, and in this article I show you how you can do it yourself.

Click through for instructions and a video.

Comments closed

Power BI and SSRS Drillthrough

Paul Turley continues a series on drillthrough. First, from a paginated report into Power BI:

In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.

Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.

After that, Paul posted a follow-up on the wherefore:

I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle.  As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.

It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.

It’s good to see interoperability between these two tools.

Comments closed

Connecting Excel to Power BI Premium Datasets

Gilbert Quevauvilliers shows how to connect Excel to a Power BI Premium dataset directly, using its XMLA endpoints:

I have had seen a few questions in the Power BI Community, as well as from other people asking if it is possible to connect directly from Excel to a Power BI Premium dataset from Excel. Without having to log into the Power BI Service or download any .ODC files.

I am going to show you how to do this below.

Read on for a screenshot-fueled demonstration.

Comments closed

Drillthrough from Power BI to SSRS

Paul Turley shows how you can drill through from a Power BI dashboard into an SSRS report:

This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).

These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

You can’t get the files just yet, but you can see what Paul does to get this working.

Comments closed