Press "Enter" to skip to content

Category: Power BI

Power BI (Lack of) Performance with Sharepoint

Published 2020-09-15 by Kevin Feasel

Matt Allington is not impressed:

On the face of it, it seems like a great idea to leverage SharePoint as a storage location for CSV and Excel files.

– Everyone has easy access to the files for editing and storage
– SharePoint manages version control, check in, check out etc
– SharePoint can facilitate shared editing of files
– You can build a Power BI report that will refresh online without the need to install a gateway.

Unfortunately, despite the benefits, the experience is not great.  Power BI performance with SharePoint as a data source is simply terrible.  Ultimately, the problems come down to performance in 2 areas.

Read on to learn more about these two issues and what you can do instead.

Comments closed

Finding the earliest and latest date across all Power BI Tables

Published 2020-09-14 by Kevin Feasel

Soheil Bakhshi has an interesting challenge:

Many of you may already thought that we can use CALENDARAUTO() in DAX and we are good to go. Well, that’s not quite right. In many cases there are some Date or DateTime columns that must not be considered in our Date dimension. Like Birth Date or Deceased Date. More on this later in this post.

In this post I share a piece of code I wrote for myself. I was in a situation to identify the Start Date and the End Date of the date dimension many times, so I thought it might help you as well.

Read on for a solution when not all date columns are relevant.

Comments closed

Conditional Merges in Power Query

Published 2020-09-09 by Kevin Feasel

Ed Hansberry shows how to perform a conditional join in Power Query:

I am going to use two tables for this example, Fact.Sales and Dim.Customer. I only want my Fact Sales table to have customer info for the Buying Group Tailspin Toys. That info is in the Dim Customer table. I can do this several ways.

1. Do the merge, expand the Buying Group column from the Customer table, then filter to only show those rows.
2. Pre-filter the Customer table for Tailspin Toys in the Buying Group column, then do the merge. It would need to be an Inner Join, otherwise you will get nulls in the Sales table when you expand, and then you have to filter those out. Which you can do, but it is more steps.
3. You can bypass all of that and do it in one step.

I’m not going to bore you with how to do methods 1 and 2. Let’s do method 3, a conditional join!

Read on for the approach, which also can take advantage of query folding when possible.

Comments closed

Automatic Retention Periods with Power Query

Published 2020-09-03 by Kevin Feasel

Gilbert Quevauvilliers has an easy method of constraining data sizes in Power Query:

How cool would it be to not have to manually update your dataset to keep data for last 2 Years (last year and this year to the current date)?

In this blog post I will show you how you can easily filter dates in Power Query to show dates for last year and year to date by using the GUI and not having to hardcode anything.

I must admit the first time I saw this was when I watching my good friend Reid Havens presenting. Thanks Reid!

Read on for the technique.

Comments closed

Optimizing Cross Join Performance in Power BI

Published 2020-09-01 by Kevin Feasel

Chris Webb tries an experiment:

In a late addition to the series of posts that I started here a few months ago, I thought it would be useful to look at how to optimise the performance of a rare (but nonetheless useful) type of merge: the cross join. Cross joins are not available as a join kind in the Table.Join or Table.NestedJoin M functions but there are two ways of creating them in Power Query:

1. Creating a custom column in your query that contains the query that you want to cross join with and then expanding, as Matt Allington describes here
2. Creating a custom column that contains a single value, such as the number 1, on both your queries and then using that column to merge the two queries on (something I mentioned a long time ago here)

Which one gives you the best performance? 

Chris has the answer to that question and the tests to prove it.

Comments closed

Power BI Migration Documentation

Published 2020-09-01 by Kevin Feasel

Melissa Coates announces some new documentation:

I’m really pleased to announce that new Power BI Migration documentation that I authored is published on the Microsoft Docs site. Although it was written from the perspective of migrating to Power BI from another platform, there’s no doubt that a lot of the content applies to a standard Power BI implementation project.

Click through to learn more and follow Melissa’s link to get the whole document.

Comments closed

Checking that Power BI Security Roles are Correct

Published 2020-08-28 by Kevin Feasel

Fred Kaffenberger poses a question:

If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.

Read on for a verification technique.

Comments closed

Passing Power Query Parameters to Stored Procedures

Published 2020-08-28 by Kevin Feasel

Soheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy.

If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.

Comments closed

Using Calculation Groups for Dynamic Measure Formatting

Published 2020-08-27 by Kevin Feasel

Matt Allington wants to solve a problem:

Using a switch measure to toggle results is a mature and common technique used in Power BI and Power Pivot for Excel. For example, a switch measure can be used to toggle what appears on a chart so that the end user can easily switch the data being visualised (see image below).

This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart. I blogged about this in 2014 here. 

The switch measure has a drawback, though: you can only have one number format, so Matt has a new solution using calculation groups.

Comments closed

Dynamic Date Column Headers in Power BI

Published 2020-08-27 by Kevin Feasel

Gilbert Quevauvilliers shows how we can display the last several days as column headers:

I find I do love a good challenge and I find that I get them frequently. I also enjoy sharing what I have learnt with others.

In this blog post I am going to show you how I found a way to create Dynamic Date Column Headers for Tables or Matrixes in Power BI.

Below is what it looks like once completed. What I did was to create a matrix with the last 5 Days (Showing each date for the last 5 days) and then along with this an MTD calculation which shows not only the header of MTD but it includes the current months values.

This is an interesting one for sure. Read on to see how Gilbert solved the problem.

Comments closed