Press "Enter" to skip to content

Category: Power BI

Dynamic Date Dimensions In Power BI

Published 2017-08-07 by Kevin Feasel

Ginger Grant shows how to build a dynamic date dimension in Power BI using either DAX or M:

Recently I needed to create a date dimension for a Power BI model as there was not one in the source database. There are two different ways that I could do this, using DAX from the Modeling Tab within the Data View or using M via the Query Editor window.  As a general rule, when it is possible data manipulation should be done in M as it offers a greater level of compression.  In this case though I am using a function in DAX, which is not the same as creating a calculated column.

Read on to see code examples for each method, as well as Ginger’s analysis.

Comments closed

Limiting Color Usage On Dashboard Charts

Published 2017-08-07 by Kevin Feasel

Jesse Gorter explains why you shouldn’t overwhelm your dashboard chart users with colors:

In this example we use a signal color for the past too. Do you notice how the usage of green distracts from the current week which is a red? This suggest we are doing great overall even though at this time, we are doing not so great. It is up to you to decide what you want to communicate. If you are a sports team showing the rank during the season, only the current position would be important. In sales, having 30 weeks of outstanding sales above the target and the current week selling slightly under, it would make sense to show the signal color for the past.

Not to mention making it easier for people with CVD to read your report, something with which the red-green scheme does not do great.

Comments closed

Stream Analytics Into Power BI

Published 2017-08-02 by Kevin Feasel

Rolf Tesmer shows off how to use Azure Stream Analytics to push data in real time via the Power BI API into your Power BI dashboard:

You can push data to the Power BI streaming dataset API in a few ways… but they generally boil down to these 3 options…

  1. Directly call the API from code
  2. Directly call the API from an Azure Logic App
  3. Use Azure Stream Analytics to push data into the API

This blog post extends on my previous post – and thus I will be leveraging Option #3 above.

Definitely worth checking out if you are interested in real-time Power BI dashboards.

Comments closed

The Great Custom Visual Move

Published 2017-07-31 by Kevin Feasel

Ginger Grant runs into a problem with some of the custom visuals (not) in the Office Store:

This week I decided to do a demo using the Aquarium custom visual.  As readers of my blog know, I have used the custom visual before, but it has been a while and I have changed PCs since then.  No worries I can always go download the visual from the store, right? Wrong. The aquarium visual is not available on the new store. Neither is Image Viewer, if one is looking to add that into your latest Power BI report it is not available. What happened?

Read on to learn why the aquarium is missing.  RIP aquarium (hopefully only temporarily).

Comments closed

Data Lake Analysis With Excel And Power BI

Published 2017-07-25 by Kevin Feasel

Sachin C Sheth announces support for Azure Data Lake Store within Excel and Power BI:

Until now, if you had to analyze data stored in ADLS with Excel, you would have to copy it into a relational data store like Azure SQL Data Warehouse or download the data onto a machine, and then use Excel to analyze that data. This was rather cumbersome involving additional cost and time. With this new support, you can now access files stored in ADLS with Excel in-place, without having to copy them to other stores or locations. You can quickly get advanced insights into raw or prepared data. Models and queries you have created using Excel that ran against local data, can be run seamlessly against data stored in ADLS.

Security capabilities of ADLS allow administrators to control access to the data stored in ADLS in a discretionary manner. With this you can limit the access that Excel users have for the data in ADLS. In this manner, data in the ADLS-based data lake continues to be the single source of truth with no redundant copies and can be analyzed by analytics tools of your own choice .

Click through for a demo video.

Comments closed

Maps In Power BI

Published 2017-07-21 by Kevin Feasel

Reid Havens shows off the different map visuals within Power BI:

ArcGIS Map

The most recent addition to the Power BI Map family. It’s supported by a company called Esri, and is a very feature rich map visual! What makes this visual stand out is that you can overlay whatever data you have with public geographical data such as demographics, weather, and even historical data. It’s highly customizable and offers multiple ways to visualize data with maps, and that’s even before you start adding the public data sets! Can you tell that I like this visual a lot? Because I do! visualize data with maps

Now I could easily spend an entire blog post JUST outlining all the ways to use this visual, but I’ll stick to the highlight reel. It can visualize data with maps using the bubble or fill method similar to the other map visuals, albiet with a few more customizations and tweaks. However, one of the unique features of this visual is the heat map option! Any of you familiar with Power Maps in Excel has probably seen this before…well now we have it in Power BI. I find this data visualization super useful in identifying data clustering based on location.

Read on for additional varieties of maps you can create.  I personally think the bubble map is ugly and that one map with pie charts (thankfully not shown in Reid’s post) is hideous, but there are some very good map visuals available to us.

Comments closed

SUM() Or SUMX()

Published 2017-07-19 by Kevin Feasel

Matt Allington explains when to use SUM() or SUMX() in DAX:

Example: Total Sales SUMX = SUMX(Sales,Sales[Qty] * Sales[Price Per Unit])

SUMX() will iterate through a table specified in the first parameter, one row at a time, and complete a calculation specified in the second parameter, eg Quantity x Price Per Unit as shown in the example above for the current filter context.  Once it has done this for every row in the specified table in current filter context, it then adds up the total of all of the row by row calculations to get the total.   It is this total that is returned as the result.

This is a really good explanation of the topic.

Comments closed

Wanted: Filtering Power BI Data Views

Published 2017-07-13 by Kevin Feasel

Rob Collie is rabble-rousing and building up a pitchfork mob:

I don’t know how we’ve managed to go this long without the ability to Filter in the Data View:

As simple as it sounds, this is the #1 thing I miss when I leave the Excel Power Pivot environment for Power BI Desktop.

Why do I miss it?  Debugging and Validating, primarily.  When I’m getting results from a measure that I don’t trust, it is super common for me to filter a table in Power Pivot so that I’m just looking at a specific set of rows.  And then I often filter by a second column, and then sometimes a third, in order to see if there are any unexpected surprises in the data.  Scrolling through thousands of rows looking with my eyes just doesn’t cut it.

This is positively crucial, at least for me, and creating a Table or Matrix visualization to perform the same task is such a high-friction alternative that I’m usually tempted to start over in Power Pivot.  Ugh.

In fact this is the overwhelming #1 reason why, if I’m starting a model from scratch, I try to do as much of the work as I can in Power Pivot before converting over to PBIX format.

Rob has provided a link to the Power BI Ideas forum, making it easy to vote this item up.

Comments closed