Press "Enter" to skip to content

Category: Power BI

Treemaps and Tables in Power BI

Published 2020-08-07 by Kevin Feasel

Ben Richardson looks at a couple of Power BI visuals:

In this article, you will learn how to work with Treemaps and Tables, which are two of the most commonly used Power BI visuals. You will also see how slicers can be used in Power BI to dynamically update the data in Treemaps and Tables. Power BI Visuals are extremely easy to create and don’t require you to write any code.

I like treemaps more than I probably should. They have a very limited set of good uses but I just can’t quit them.

Comments closed

API Consumption with Power BI Dataflows

Published 2020-08-06 by Kevin Feasel

Matthew Roche covers some pointers when working with APIs:

These APIs are often slower than a database, which can increase load/refresh times. Sometimes the load time is so great that a refresh may not fit within the minimum window based on an application’s functional requirements.

These APIs may also be throttled. SaaS application vendors often have a billing model that doesn’t directly support frequent bulk operations, so to avoid customer behaviors that affect their COGS and their bottom line, their APIs may be limited to a certain number of calls for a given period.

The bottom line is that when you’re using APIs as a data source in Power BI, you need to take the APIs’ limitations into consideration, and often dataflows can help deliver a solution that accommodates those limitations while delivering the functionality your application needs.

Read on for more details including links to a few pointers.

Comments closed

Filtering out Blanks in MEDIANX with DAX Studio

Published 2020-08-04 by Kevin Feasel

Matt Allington continues a series on blanking out:

This article is a follow on from last week. I recommend you go back and read the article first if you missed it, but in summary, I want to write a measure (not a calculated column) that will return the median sales of products while excluding the products with blanks (no sales). As I showed last week, this is relatively easy with a calculated column. Here it is again.  Remember writing calculated columns first is a great way to visualise the problem you want to solve.  It is not a great way to solve most problems (some yes, most, no).

Read on to see how you can solve the problem using DAX Studio.

Comments closed

Custom Formatting of Visuals using Calculation Groups

Published 2020-08-04 by Kevin Feasel

Gilbert Quevauvilliers shares some exciting news:

The Power BI team has been doing a lot of incredible work. The most recent update which I got wind of is Custom Formatting of measures is now supported for Visuals.

This has already been deployed to the Power BI Service and if you download the
latest version of Power BI Desktop (Version 2.83.5894.961 as at 03 Aug 2020) it has the new features. This means you can use this TODAY!

Previously this was only supported for tables and matrixes.

Click through to see how it looks in Power BI. It’s easy, and that’s a good thing.

Comments closed

Issues when Using the Power BI REST API

Published 2020-08-03 by Kevin Feasel

Nicky van Vroenhoven walks us through a few issues discovered when trying to use the Power BI REST API:

Last month when I was trying to follow the excellent video on Building a Power BI Admin View by Parker Stevens ( b | @PowerBIElite | YouTube).

However, I ran into a few things that I’d like to dedicate this quick post on.

Read on to see where Nicky ran into issues and how you might be able to avoid them.

Comments closed

Wanted: Limiting Calculation Group Members in Power BI

Published 2020-08-03 by Kevin Feasel

Marco Russo shares an annoyance in how calculation groups are currently set up:

The consumption experience following the introduction of calculation groups needs to be improved in Power BI. For example, the immediate advantage of having a time intelligence calculation group is that it is no longer necessary to create multiple variations of the same measure to implement all the possible time intelligence calculations. However, what happens when you have a matrix with Sales Amount, Total Cost, Margin, but you want to show the current value for all the measures and the year-over-year difference only for the Margin measure? Today, if you add a year-over-year calculation item to the matrix, you get the year-over-year of all the measures.

Marco has a Power BI Idea to resolve this which sounds a lot like GROUPING SETS in T-SQL.

Comments closed

X Functions in DAX

Published 2020-07-31 by Kevin Feasel

Matt Allington explains what that “X” means in functions like MEDIANX and also builds out a problem to solve:

An X function is a class of functions in DAX that are also known as “iterators” (note, there are other iterating functions too, like FILTER, but I am only referring to the X functions here). It is a big topic on its own, and this article is not going to be the definitive guide to X functions. But I will give you a couple of insights.

I have learnt a lot about how to teach people DAX over the last 6 years, and my teaching methods have evolved over that time. I remember fondly speaking at the Microsoft Data Insights Summit with Will Thompson on the topic “DAX 50 – DAX for the rest of us“. Will said to me “don’t mention the word ‘iterator’ as it is too confusing.”. I didn’t agree with Will at the time, but his comment stuck with me. Over time I have changed the day way I teach DAX. These days I show people how to add a calculated column in a table (everyone can do that – its dead easy). Then I explain that an X function does exactly the same thing, it’s just that you can’t see the interim results materialised in front of their eyes.

Click through to see what Matt means and stay tuned for the next episode of the X Functions, where Matt reveals that there are aliens by use of bi-directional relationships.

Comments closed

Converting Hexidecimal to Decimal with Power BI

Published 2020-07-31 by Kevin Feasel

Soheil Bakhshi has a great function for us:

A while ago I wrote a blogpost on how to use Unicode characters in Power BI. In that blogpost I used a recursive Power Query function to convert Hex values to Dec values. A few weeks back one of my site visitors kindly shared his non-recursive version of Power Query function which beautifully does the job. A big shout out to Rocco Lupoi for sharing his code. So, I decided to share it with everyone so more people can leverage his nice Power Query function. I have touched his code a bit though, but it was more cosmetic change, so all the credits of post goes to Rocco. 

Click through for the details.

Comments closed

Creating Power BI Measures via Visual Studio Code

Published 2020-07-30 by Kevin Feasel

Phil Seamark goes one step further with TOM:

My last blog introduced the idea of using Microsoft Visual Studio Code to work with Power BI Models. For this article, I build on that idea by showing how you can use a TOM based script to automatically generate measures in your model Power BI (or Azure Analysis Services) model.

For simplicity, the example in this blog will do the following:

– Connect to an instance of Power BI Desktop
– Iterate through every Table in the model
– Iterate through every Column in the “current” table from the outer loop
– If the Column is numeric and not hidden, create a simple [Sum of <column>] measure

Read on for demonstration code and a walkthrough of the process.

Comments closed