Press "Enter" to skip to content

Category: Power BI

Incremental Refreshing in Power BI Premium Per User

Published 2021-06-23 by Kevin Feasel

Gilbert Quevauvilliers continues a series on migrating from Azure Analysis Services to Power BI Premium Per User:

Welcome to the fourth instalment of my series on migrating AAS to PPU.

In this blog post I am going to cover the differences, and pros and cons to incremental refreshing when migrating from AAS to PPU.

The reason for incremental refreshing is that I do not have to reload the entire fact table every day when new data arrives.

This also allows for new data to be updated quicker.

And finally, it also uses less resources (memory and CPU) when refreshing the datasets.

Read on to see how it works in both platforms.

Comments closed

Returning 0 Instead of BLANK in DAX

Published 2021-06-22 by Kevin Feasel

Marco Russo and Alberto Ferrari want to see zeroes in specific circumstances:

What makes this specific product interesting is that the product had sales in 2007, no sales in 2008 and it started selling again in 2009. Its behavior is different than the other products. Indeed, for most of these products one can argue that they start to produce sales when they were introduced in the market. Their behavior is quite intuitive: no sales up to a given point in time, then they start selling. We want to highlight this specific product because it shows a gap in sales when it was already present on the market. For other products, we are happy to blank them until their first sale. By doing this, we show gaps when they are real, and we avoid showing non-relevant information, that is products that could not produce sales because they were not even available to sell.

Read on to see how they do this.

Comments closed

Exporting Large Tables from Power BI Desktop

Published 2021-06-22 by Kevin Feasel

Nick Edwards shows us the way:

Have you ever wanted to export a table from Power BI Desktop into Excel just to make sure the DAX you’ve written is performing as expected but ran into this error message: “Data exceeds the limit – Your data is too large. Some data sampling may occur”?

Most probably this has occurred because you’ve got more than 30,000 rows of data in your table that you’re trying to export. In the example shown below I’ve actually got 30,001 rows of data – a row containing column headers plus 30,000 rows of actual data.

Click through to see how you can get around this limitation.

Comments closed

Nullable Columns and Power Query

Published 2021-06-21 by Kevin Feasel

Chris Webb gives us another reason to curse NULL:

Recently I’ve been asked by colleagues with various different types of performance problems why Power BI is generating SQL in a particular way, and the answer has been the presence of nullable columns in the underlying database – whether it’s SQL Server, Snowflake or Databricks. Now I’m not a DBA or any kind of database tuning expert so I can’t comment on why a SQL query performs the way it does on any given platform, but what I can do is show you two examples of how the presence of nullable columns changes the way Power BI and Power Query generate SQL.

Click through to see what happens.

Comments closed

Reasons to Switch to Power BI Gen2

Published 2021-06-18 by Kevin Feasel

Teo Lachev gives us five reasons to switch to Power BI Gen2:

More memory
Imported models are memory-resident so memory is usually the most constraining factor. With Gen2, the capacity maximum memory applies to the resource itself and not collectively across all resources in the capacity. Let’s say you are on a P1 plan which has a maximum memory capacity of 25GB. With Gen 1, you won’t be able to have two datasets, let’s say 20GB and 10GB, loaded at the same time. However, Gen2 will apply the 25GB limit to each dataset. So, each resource (dataset, report, dataflow) will be boxed within 25 GB. This feat is possible because Gen2 uses a SaaS approach, which means datasets are scattered across multiple cluster nodes instead of being associated with a dedicated capacity. A potential downside, however, could be “noisy neighbor” because a P3 cluster node may co-host datasets from different customers.

Read on for the full list of reasons, as well as three things Teo would like to see improved in a future release.

Comments closed

Loading Data into Power BI Premium Per User vs Azure Analysis Services

Published 2021-06-16 by Kevin Feasel

Gilbert Quevauvilliers continues a series on moving from Azure Analysis Services to Power BI Premium Per User:

I have been working with a customer where I have got data in AAS and in PPU for the same dataset.

What I have found is that when the data is loading it is very similar in terms of how long the data takes to load.

With one of my customers as an example the data was being curated in Asia, whilst the business was running things from Australia. By hosting AAS/PPU where the data was curated meant that the data loading was significantly faster. Yes while the reports would have to access the data across the ocean, this only sends the results, so the performance of the reports was and is still blazingly fast!

Click through for the full story.

Comments closed

Embedding Power BI into Jupyter Notebooks

Published 2021-06-15 by Kevin Feasel

Dennes Torres takes a look at a new Power BI feature:

Microsoft recently announced the ability to include Power BI reports inside Jupyter notebooks. After overcoming the dazzle of this exciting feature, what comes to my mind is: “Why do we need this?”

I’m far from being a Jupyter notebook expert, but as far as I know, they are used for interactive analysis. Why, in the middle of an interactive analysis, would I need to get a Power BI Report?

Even if the Power BI Report is not exactly what I need, I could continue the analysis in Power BI. Why should I move it to Jupyter and make this kind of integration with an existing report?

Read on to see what you can do with it. As far as how you might be able to use it, that remains an open question.

Comments closed

Changing Power BI Evaluation Container Numbers

Published 2021-06-14 by Kevin Feasel

Chris Webb shows how we can optimize the number of evaluation containers in Power BI:

Last week I showed how the new MaxEvaluationWorkingSetInMB registry setting could increase the performance of memory-hungry Power Query queries in Power BI Desktop. In this post I’ll show how the other new registry setting, ForegroundEvaluationContainerCount, can also help performance. Before I carry on I recommend you read the documentation on these new registry settings if you haven’t done so already.

To illustrate the effect of this setting I created ten identical Power Query queries feeding an Import mode dataset in a new .pbix file, each of which read data from the same 150MB CSV file, apply the a filter and then count the number of rows returned. 

I don’t think I like having to modify a registry setting each time; that’s leading me to believe I should rarely (or never) mess with this.

Comments closed

Creating Power BI Themes

Published 2021-06-14 by Kevin Feasel

Adam Aspin takes us through creating Power BI themes:

Power BI has taken the world by storm when it comes to creating attention-grabbing dashboards that empower users. It has come to dominate the analytics arena with its ease of use, wide range of connectivity options, and the variety of available visuals. However, formatting (and reformatting) dashboard visuals can prove time-consuming and repetitive – as can standardizing the presentation of multiple dashboards to create a unified look and feel for a suite of reports. Most users would rather spend their time analyzing and delivering meaningful insights as opposed to applying colors and font choices to charts and tables. This article explains how Power BI themes can avoid that tedious work!

This is where the creation and application of Power BI themes comes in. A theme is a standardized definition of some – or all – of the formatting of a dashboard. This can range from defining a color palette and a selection of font choices to the detailed specification of each and every visual. Applying a theme allows you to format virtually every visual in a dashboard instantly. What is more, any changes that you subsequently make to a theme can be reapplied in a few clicks to update your dashboard’s presentation. Themes can be created once, then applied to dozens or even hundreds of Power BI dashboards to guarantee a coherent and rigorously standardized presentation style across a department or even an entire organization.

Read on to see some of the things you can do with customizing Power BI.

Comments closed