Press "Enter" to skip to content

Category: Power BI

Comparing GROUP BY and SUMMARIZE in DAX

Published 2023-08-17 by Kevin Feasel

Marco Russo and Alberto Ferrari make a comparison:

DAX offers a rich set of functions, some of which overlap in their functionalities. Among the many, two functions perform grouping: SUMMARIZE and GROUPBY. These are not the only two: SUMMARIZECOLUMNS and GROUPCROSSAPPLY perform similar operations. However, the article is about SUMMARIZE and GROUPBY, as the other functions have many more functionalities, so a comparison would be unfair.

To make a long story short: GROUPBY should be used to group by local columns, columns created on the fly by DAX functions. SUMMARIZE should be used to group by model and query columns. Be mindful that both functions support both scenarios: both functions can group by model and local columns. However, using the wrong function translates into a strong decrease in performance.

Read on for a detailed explanation.

Comments closed

Cross-Database and Cross-Cluster ADX Joins in Power BI

Published 2023-08-17 by Kevin Feasel

Dany Hoter makes a connection:

You may have more than one ADX database and probably more than one ADX cluster.

In some cases, you want to join tables or functions from more than one database/cluster.

In this article you’ll see how to make sure that such joins are folded and sent to the ADX backend instead of executing at the level of the Power Query mashup engine.

Everything mentioned here is applicable to Azure Data Explorer, Synapse Data Explorer, and Fabric RTA.

Read on for the two examples.z

Comments closed

Creating a Calendar View in Power BI

Published 2023-08-16 by Kevin Feasel

Martin Schoombee needs a calendar:

It’s pretty sad that we don’t have a built-in calendar visualization in Power BI, and the custom visuals in the marketplace don’t have everything I need/want for my own internal reporting…so I decided to experiment a little and see how close I could get with the standard graphs that are available.

Read on to see how close Martin could get. It’s actually more calendar-looking than I would have expected, though also frustratingly limited.

Comments closed

Creating Wireframes in Power BI

Published 2023-08-15 by Kevin Feasel

Seth Bauer has my attention:

In the dynamic landscape of data visualization, staying ahead of the curve is crucial. That’s why we’re thrilled to introduce the latest game-changing feature in the PowerBI.Tips Theme Generator: WireFrames! This innovation not only revolutionizes the way Power BI is customized but also opens doors for UI/UX teams and designers to interact with Power BI developers like never before.

Most of this does require a subscription, but it does look quite interesting.

Comments closed

Creating a Time Dimension in Power BI via DAX

Published 2023-08-14 by Kevin Feasel

Angela Henry gets a watch:

There are some instances when you want to analyze data over time, not just dates. Most of us are familiar with having to create date tables and use them in analysis, but having to analyze data over time is not as common. Let’s say you run a taxi company and you want to determine when your busiest times of day are. This would come in handy for scheduling drivers. You need more drivers during busy times because no one wants to wait for a taxi!

Read on to see one way to create the table in Power BI.

Comments closed

Power BI and Eventual Browser Development

Published 2023-08-14 by Kevin Feasel

Chris Webb talks about the present and the future:

Turning the question around, however, leads you to some aspects of the question that haven’t been fully explored. Instead of asking “Can I run Power BI Desktop on my Mac?”, you can instead ask “Can I do all of my Power BI development using only a browser?”. At Microsoft our long-term goal is to make all Power BI development web-based, but how close are we to that goal?

Read on for Chris’s answer.

Comments closed

From Join to Lookup in KQL on Power BI

Published 2023-08-10 by Kevin Feasel

Dany Hoter gives us a workaround:

Many users who try  ADX in direct query mode encounter errors right away.

The errors  complain about lack of memory.

 If the tables are small enough, it may work but still performance will not be as advertised on TV.

The reason in most cases is the behavior of joins in ADX as they are created by PBI.

In this article I’ll show different approaches to joining tables as used by PBI for related tables or as can be expressed in KQL in general.

I created a special table in the help cluster with 31 million rows that is big enough to demonstrate the performance differences between the variations.

Read the whole thing. This one’s a little surprising to me.

Comments closed

Refreshing Excel in SharePoint without Power BI Gateways

Published 2023-08-09 by Kevin Feasel

Gilbert Quevauvilliers needs a refresh:

I have been answering some questions on the Power BI (Or is it now Fabric?) community forum, and I have found recently that there are some people asking how to get data from Excel that is stored in SharePoint Online.

In this blog post I will show you how to connect to a single Excel file using Power BI desktop, and then configure it in the Power BI Service where it does not require a gateway.

Read on to see how.

Comments closed

Cross-Database Query Folding in Power Query

Published 2023-08-07 by Kevin Feasel

Chris Webb doesn’t want query folding limits:

A few months ago a new option was added to the Sql.Database and Sql.Databases functions in Power Query in Power BI and Excel which allows Power Query queries that combine data from different SQL Server databases to fold. Here’s a simple example showing how to use it.

Read on for the setup and the demonstration. This is pretty nice for cases where you have user data in one database and reference data in another, or you’re trying to join together data from two separate systems.

Comments closed