Press "Enter" to skip to content

Category: Power BI

Creating Calculation Groups in Power BI Desktop via Powershell

Published 2022-06-20 by Kevin Feasel

Phil Seamark helps those who are stuck lacking tools:

I recently shared on Twitter a 7-module learning path on MS Learn that teaches all you need to know about calculation groups in Power BI. This learning path is an excellent course, and I highly recommend it. However, as part of this exchange, I received a reply from someone lamenting that calculation groups are unusable in organisations that will not allow non-Microsoft applications. Power BI Desktop does not currently have UX enabling you to create/manage calculation groups in a Power BI Model, so the most common method today is to use 3rd party tools such as Tabular Editor.

This exchange is not the first time I have heard this feedback, so I decided to share a technique showing how you can use Microsoft tools. The approach used in this article uses PowerShell but can quickly get translated to VS Code or other scripting environments.

Click through if you’re in that unfortunate situation.

Comments closed

Building a Rank in Power Query

Published 2022-06-20 by Kevin Feasel

Reza Rad ranks things:

Although, calculating rank dynamically is often useful, It is not rare to need to do ranking on a pre-calculated basis. Let’s say, for example, you want to create an aggregated table by customers and year. and you want to rank customers based on their sales amount in that table. the period and other factors are remaining static. This pre-calculated aggregated table can speed up the performance of your report significantly in the future. In a case like this, the rank calculation can be done as a pre-calculation. And when you do this as a pre-calculation, Power Query is a useful option. Because then you can do that even in a Dataflow, and the result can be used in multiple Power BI datasets.

Click through to see how you can do it, as well as what Power Query calls its equivalents to ROW_NUMBER(), RANK(), and DENSE_RANK().

Comments closed

Extracting Data from DAX Measures into CSV

Published 2022-06-17 by Kevin Feasel

Gilbert Quevauvilliers builds a process:

In this blog post I am going to demonstrate how to use the new Power Automate Flow to extract data from a DAX measure into a SharePoint CSV file.

I got this idea after reading the blog post from the Microsoft Power BI Team: Unlocking new self-service BI scenarios with ExecuteQueries support in Power Automate | Microsoft Power BI Blog | Microsoft Power BI

The great news is that this works on Power BI Pro, Premium Per User and Premium.

Read on to see how.

Comments closed

Using SELECTEDVALUE with Field Parameters

Published 2022-06-13 by Kevin Feasel

Marco Russo diagnoses an error:

If you try to use SELECTEDVALUE on the visible column of the table generated by the Fields Parameters feature in Power BI, you get the following error:

Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Read on to understand what causes this error and what you can do to resolve it.

Comments closed

Implementing a Power BI Composite Model

Published 2022-06-09 by Kevin Feasel

Marc Lelijveld walks us through an implementation of the Power BI composite model:

Have you been working with Composite Models in Power BI? Did you run into challenges while you did? Then this blog is for you! In this blog I will further elaborate on what the composite models (including DirectQuery for Power BI datasets) are and everything that comes into play when you start implementing them.

During this blog I will introduce you to topics like source groups, storage modes and relationship evaluation. All different topics that come into play when you start building composite models.

If composite models sound interesting, be sure to check this out.

Comments closed

Reviewing Power BI Field Parameters

Published 2022-06-08 by Kevin Feasel

Teo Lachev is pleased:

Coming back from a long vacation and I almost missed this new Power BI killer feature: Field Parameters! Not to be confused with Dynamic M Query Parameters that I ranted about here, field parameters solve a long-standing limitation of Power BI that prevents you to bind dynamically dimension members to a visual. Dynamic binding wasn’t issue with measures because they are dynamic and can evaluate runtime conditions, such as slicer selection. But dimensions were a different story. Once you have bound them to a category bucket in a visual, you couldn’t change them on the fly.

Read on for more information on a common scenario in which field parameters can be quite helpful.

Comments closed

KEEPFILTERS in DAX

Published 2022-06-07 by Kevin Feasel

Marco Russo and Alberto Ferrari explain how the KEEPFILTERS function works:

KEEPFILTERS is a CALCULATE modifier used to change the way CALCULATE merges new filters with the outer filter context. Indeed, the default behavior of CALCULATE is to override existing filters. By using KEEPFILTERS you ask CALCULATE to add the new filter to the outer filter context, instead of overriding the outer filter.

Read on for the explanation and a demo.

Comments closed

Reviewing Power BI Datamarts

Published 2022-06-07 by Kevin Feasel

Teo Lachev looks at Power BI Datamarts:

As Microsoft announced here, Power BI datamarts are upon us. I can almost see an important enterprise client demanding “self-service datamarts me now or else… “, thus inspiring an opportunity for another premium feature, spearheaded with great vision and effort, but questionable practical value. In a nutshell, a Power BI datamart is a combo of Power BI Premium and a Microsoft-hosted Azure SQL Database aiming to simplify the implementation of a departmental datamart.

This take is a bit more negative than most of the others I’ve seen, so it’s worth a read in comparison to what others have written.

Comments closed