Press "Enter" to skip to content

Category: Power BI

Color Band by Group in Power BI

Published 2020-04-30 by Kevin Feasel

Marco Russo and Alberto Ferrari show how we can change color alteration to switch from row to row and instead go from group to group:

The background color of the rows depends on Sales[Order Number]. The background color switches between white and light gray every time the order number changes, so all the rows of the same order have the same background color and can be easily identified. You cannot obtain this visualization by only using a Power BI style, because the coloring of a row depends on the actual data in it. You can achieve this goal by using the conditional formatting feature in Power BI. You can set the background color of a cell according to the value of a measure. Therefore, you need a DAX formula that returns two values: one for the white rows and one for the gray rows. The value returned by the measure must alternate between those two values with each consecutive order number.

Read on for an example of how you can do this.

Comments closed

Progressive Disclosure in Power BI

Published 2020-04-30 by Kevin Feasel

Prathy Kamasani takes us through the implementation of a design idea in Power BI:

In the above example, I used a pattern to show details using action from the Card. When a user clicks on a card, the report will show details related to Card. It sounds straightforward, but it involves a lot of work using Power BI Functionalities: Buttons, Bookmarks, Sections, Grouping and Page Size.

There are few aesthetics I paid attention in this Report Page which are key for any landing page. Usually, a Landing page helps users to navigate around the Power BI Model, so it is important to highlight those navigation steps. In the above model, I used Buttons, labels and Images for navigation hints.

I like this for some uses, like giving analysts a chance to dive into the data. For an operational dashboard, I don’t like it very much unless the cards at the top alone provide me enough information to know whether I need to take an action; otherwise, it loses one of the most important concepts of a dashboard, glanceability.

Comments closed

Using SQL Server Scalar Functions with Power Query

Published 2020-04-30 by Kevin Feasel

Erik Svensen shows that you can call user-defined scalar functions in SQL Server from Power Query:

Currently I am working with a project where we extract data from a SQL server – some of the business logic is built into scalar value functions (documentation).

Now the magic of PowerQuery enables us to reuse these functions within PowerQuery and Query Folding is supported – more about this at the end of this post.

My initial reaction is “That way lies madness” but in moderate doses, I could see this as a valuable second-best option for teams pulling data into Power BI.

Comments closed

Building a P&L Statement with Power BI

Published 2020-04-29 by Kevin Feasel

Matt Allington has the need for some financials:

A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how to do this way back in 2015 when I met Derek Rickard. I was still fairly early on my DAX journey at the time and certainly didn’t have any experience with financial accounting using DAX. Derek shared with me how he built his P&L using Adventure Works. I have used these principles many times to help customers, and I thought it would be a great time to pass on that knowledge, and the skills I have learnt along the way on my blog.

Click through for a video and the measures used.

Comments closed

Aggregations in Power BI Desktop

Published 2020-04-27 by Kevin Feasel

Jeroen ter Heerdt tries out aggregations in Power BI Desktop:

Aggregations bring me back to the good old SSAS Multidimensional days. The days that I invariably built the aggregations tree the wrong way around, SSAS would complain, I would scream and eventually give in.

You can imagine that I was curious but skeptical when I tried aggregations in Power BI Desktop. I was afraid of ending up in the same hate-but-need relationship that I had with SSAS multidimensional when it came to aggregations.

The good news? It is not like that. At all. Once you have aggregations working, they are great.

The not so good news? It took me longer than I am willing to admit getting them working – primarily due to data types and creation of the aggregated table. More details in this blog post.

Read on for the full story.

Comments closed

Sentiment Analysis with Power BI

Published 2020-04-27 by Kevin Feasel

Teo Lachev takes us through two options available for sentiment analysis with Power BI:

Cognitive Services is an Azure PaaS cloud service that supports text analytics and image recognition. It’s automatically included in Power BI Premium or Embedded capacities (make sure that AI workloads are enabled in the capacity settings). If you organization doesn’t have Power BI Premium or Embedded, you can provision Cognitive Services in Azure (requires an Azure subscription) and then write a custom Power Query function to invoke its APIs, as demonstrated by this tutorial. If you provision Cognitive Services outside Power BI Premium,  you’ll be charged per transaction. In the case of Power BI, the number of transactions equates to the number of rows in your table. So, if you refresh five times a table with 1,000 rows and calculate the sentiment polarity score for each row, you’ll be charged for 5,000 transactions.

Read on for the full report on each option.

Comments closed

Moving Queries to and from Power BI Desktop and Excel

Published 2020-04-24 by Kevin Feasel

Erik Svensen shows how we can copy queries from Power BI’s Power Query Editor into Excel and back:

Today I discovered a way to easily copy all queries (including the groups) from a PBIX file to Excel without entering the power query editor in Excel.

In my case I needed to copy the data returned by a query and find it some much easier to get the data using Excel instead of generating a csv file from a visual in Power BI Desktop and importing that into Excel.

Click through for a step-by-step demonstration of the process.

Comments closed

Dynamic String Formats for Power BI Calculation Groups

Published 2020-04-23 by Kevin Feasel

Kasper de Jonge shows how we can build out different format strings based on calculation groups in Power BI:

One of the cool things of calculation groups is they cannot just be used to apply a calculation over your “base measure” but also apply a dynamic formatstring. You could do this without calculation groups before too as I described here. This method had one big drawback though, it uses the FORMAT function and when doing that all results are transformed into strings. This does help in showing the right format, but you lose a lot of other functionality like sorting or conditional access. Now with calculation groups we can do custom formatting and keep the data type. How does that work?

Read on for an example.

Comments closed

Refreshing Selected Tables in Power BI Desktop

Published 2020-04-22 by Kevin Feasel

Gilbert Quevauvilliers answers one of the great mysteries in life:

When working with Power BI datasets I often want to refresh multiple tables, BUT not all the tables in my model. (This often happens when I made a change to underlying data sources and I want to make sure it is working as expected in Power BI Desktop)

I recently found that I could do it by doing the following steps below.

When you have one or two big tables and you don’t make any changes to those tables, it can be a pain waiting for them to refresh. This is a great alternative.

Comments closed