Press "Enter" to skip to content

Category: Power BI

Switching Between Dates with Calculation Groups

Published 2020-12-08 by Kevin Feasel

Alberto Ferrari has another good use for calculation groups:

This technique works just fine; it has the disadvantage of creating many measures, one for each combination of relationship to activate and base measure. Another solution is to create a calculation group that changes the active relationship of the selected measure. Doing this, you create one calculation item for each relationship and the user chooses the relationship to activate using a slicer or a report filter.

But read on for the calculation group solution, which is a clever way of deferring which relationship you care about until the user selects it.

Comments closed

Moving Power BI Dataflows Across Workspaces

Published 2020-12-07 by Kevin Feasel

Mark Lelijveld has updated a script for us:

Over a year ago, I wrote a blog about moving dataflows across workspaces using a PowerShell script. Especially useful if you want to move dataflow logic from your development to test, acceptance or production workspace.

I received a bunch of feedback on this script and run into some issues myself as well lately. It was about time for an update of the script! Below I share the issues that are addressed in this new version and what new additions are added to the script.

Click through for details on the update.

Comments closed

Power BI Premium Per User

Published 2020-12-04 by Kevin Feasel

Adam Saxton is excited:

Are you curious what Power BI Premium Per User is all about? Adam walks you through how to get it and what it means from a user experience. Take advantage of Power BI Premium features without the Premium capacity price!

Click through for the video as well as a few links for more info.

Comments closed

Beyond 10GB for Power BI Users

Published 2020-11-27 by Kevin Feasel

Gilbert Quevauvilliers wants to go to infinity and beyond:

By default, when using Power BI Premium or Power BI Premium per user the dataset size is set to 10GB.

I have had the wonderful experience of refreshing my dataset and getting the following error:

In the steps below I will show you how to change this setting to allow for larger dataset sizes.

There are a few steps involved, but hey, if you’re paying for Premium, it’s worth a few steps to get this.

Comments closed

Contingent Power BI Dataset Refreshes

Published 2020-11-25 by Kevin Feasel

Chris Webb has an interesting problem to solve:

This week a customer came to me with the following problem: they had scheduled the refresh of their dataset but their source data wasn’t always ready in time, so the old data was being loaded by mistake. The best solution here is to use some kind of external service (for example Power Automate) to poll the data source regularly to see if it’s ready, and then to refresh the dataset via the Power BI REST API when it is. However, it got me thinking about a different way of tackling this: is it possible to write some M code that will do the same thing? It turns out that it is, but it’s quite complicated – so I don’t recommend you use the code below in the real world. Nevertheless I wanted to write up the solution I came up with because it’s interesting and you never know, it might be useful one day.

Read on for the less-than-optimal solution, but do check out the better solution Chris describes.

Comments closed

RANKX on Multiple Columns in DAX

Published 2020-11-24 by Kevin Feasel

Alberto Ferrari walks us through ranking based on multiple columns:

DAX offers the RANKX function to compute ranking over a table, based on measures or columns. One limitation of RANKX is that it is only capable of ranking using a single expression. Oftentimes it is necessary to use multiple columns to obtain a ranking, either because the business requirement dictates it, or because you want to rank ties with different criteria.

As a demonstration, we rank customers based on their purchase volume. To artificially introduce ties, we use the Rounded Sales measure, that rounds the sales amount to the nearest multiple of one thousand. Using Rounded Sales, several customers show the same amount of 10,000.00. Because they are ties, their ranking must now be defined by alphabetical order based on their names.

Read on for two methods to solve this problem.

Comments closed

DAX Formatter for Power BI Desktop

Published 2020-11-24 by Kevin Feasel

Phil Seamark has a new tool for us:

Last week I was honoured to take part in the latest edition of the Power BI Dev Camp which is run by my colleague Ted Patterson. It was a fun session which I enjoyed.

As part of the Dev camp, I walked through some of my recent Visual Studio Code based blog posts on how to perform various tasks against models hosted in Power BI desktop.

While preparing for the session, Ted and I agreed that it might be helpful to create a small external tool that could automatically format all DAX expressions in a Power BI model. The idea is to leverage the excellent DAX Formatter API provided by the good folks at SQLBI. This API is the same endpoint used when you format your DAX using DAX Studio.

Read on for more details.

Comments closed

Power BI the Right Way: Separating Data Models and Reports

Published 2020-11-23 by Kevin Feasel

Paul Turley continues a series on doing Power BI the right way:

Back in the day, when we created BI solutions, reports and data models were separate. If you created a cube or Tabular model with Analysis Services, it was developed with Visual Studio and deployed to a server. Reports could be authored and deployed to the report server, separately. Now, with Power BI Desktop, you author your data model and report in the same development space and then deploy the whole kit and kaboodle to the service at once.

Secretly, Power BI actually separates the report from the data model (called a dataset in the service) and gives them both the same name. This is very convenient for self-service projects because it is quick and easy to make changes to the data model, queries and measures if you need to make a report enhancement. This is all well and good for small, one developer projects but what about those larger scale solutions where the data model and reports are developed by different folks, or when multiple reports are connected to the same data model?

At what point does it make sense to separate the data model and reports into separate files?

Read on and let Paul illuminate.

Comments closed