Press "Enter" to skip to content

Category: Power BI

Immediate Streaming and the Power BI Data Gateway

Published 2019-09-13 by Kevin Feasel

Gilbert Quevauvilliers doesn’t have time to wait:

Currently as far as I understand it the On-Premise Data Gateway will wait and buffer some data before sending it through to the Power BI Service. By changing the setting below in the On-Premise Data Gateway, it will start streaming the data almost immediately.

I am fortunate enough to be really good mates with Phil Seamark who so part of the Power BI CAT team and he gave me a little nugget of gold that I would like to share with you.

Read on to see how to configure the gateway to stream immediately.

Comments closed

Renaming Multiple Columns with Power Query

Published 2019-09-12 by Kevin Feasel

Matt Allington shows how you can use M to rename all columns at once in a table in Power Query:

When you are using a matrix like this, it can be difficult to tell which “Year” column is coming from which table, as shown below.

One solution to this problem is to rename all the columns in each table by pre-pending Order or Delivery to the front of the existing column names.  Once that is done, it is much clearer which column is which.

Matt describes the concept for you, but also has a video showing how to do this.

Comments closed

3D Effects in Power BI

Published 2019-09-10 by Kevin Feasel

David Eldersveld shows how you can use orthographic projection in Power BI:

The projection from three coordinates to a 2D plane is achieved by adding the following two measures. Be sure to adjust the column references and what-if parameter names at the top to correspond to your own data.

Here’s my “Ortho x” measure. The initial six bold values are what you’d need to adjust to your own data and parameter names.

David lays out a face, which is pretty neat.

Comments closed

Progressive Disclosure

Published 2019-09-06 by Kevin Feasel

Marc Lelijveld continues a series on storytelling with Power BI:

Progressive disclosure
It is all about giving that little bit more insights which can be done in many ways. For example, you want to show the sales by product category, which you’ve put in a bar chart. Looking at these bars, you might be interested in the number of manufactures involved in these sales amounts for product category. You can create a stacked barchart representing the different manufacturers in a legend. Or you can use another chart in your report to represent the top 5 products, which will interact with the sales over time chart. But both options will use additional space on your report canvas and look a bit messy, which can distract the users of where it is all about.

Marc is wrapping up the series and it’s worth the read.

Comments closed

Multi-Column Slicers in Power BI

Published 2019-09-04 by Kevin Feasel

Marco Russo shows us how we can filter on multiple columns in a single slicer in Power BI:

Power BI provides slicers for a single column, but there are scenarios where it could be useful to consolidate alternative filters for multiple columns in a single slicer. Technically, this is not possible in Power BI through the standard visualizations, but you can use a particular data modeling technique to obtain the desired result.

Consider the case of a Customer table with a geographical hierarchy with Continent, Country, and State. The requirement is to enable a filter over California (State), France (Country), or Asia (Continent) using a single slicer

Marco takes us through the process and offers up a clever solution.

Comments closed

Power BI Desktop to SSAS Error with No Models

Published 2019-09-04 by Kevin Feasel

Shabnam Watson takes us through various cases where you might hit an error connecting your Power BI Desktop installation to an SSAS server:

I recently ran into an error while connecting live from Power BI Desktop to a SSAS server. Everything was on-premises.There was no cloud component involved. I had full admin rights to the SSAS server and could see all databases and models from SSMS and other tools but was getting this error from PBI Desktop.

The Server you’re trying to connect to doesn’t have any models or you don’t have permissions to access them.

If I explicitly specified the name of the database I wanted to connect to, then it would connect and show me the database contents but if I did not specify the database name, I would get this error. The question is why?

Read on for several possible answers.

Comments closed

Understanding Power BI Data Virtualization Queries

Published 2019-08-30 by Kevin Feasel

Gerhard Brueckl walks us through a few examples of queries Power BI makes when virtualizing data:

Even though this query only touches two different data sources, it is a good way to analyze the queries sent to the data sources. To track these queries I used the built-in Performance Analyzer of Power BI desktop which can be enabled on the “View”-tab. It gives you detailed information about the performance of the report including the actual SQL queries (under “Direct query”) which were executed on the data sources. The plain text queries can also be copied using the “Copy queries” link at the bottom.

Read on for the queries and for Gerhard’s analysis.

Comments closed

Two Takes on Power BI Time Dimensions

Published 2019-08-28 by Kevin Feasel

Brett Powell gives us a version of a time dimension in Power Query:

Like most dimensions in a data model, users will generally begin their analysis of time at a high level (e.g. hour) and then filter and drill their way into greater levels of detail, often leveraging hierarchies built into the model and/or drilling functionality in tools like Power BI. To support analysis by multiple grains and hierarchies, the query produces columns which group the seconds into hourly quartiles, hours, and minutes.

Gilbert Quevauvilliers has a script to generate a time dimension as well:

Below is the syntax to create a Time Dimension Table in Power Query

I had a requirement where I needed to create a Time Dimension for a customer. Most of the time I only need the date. Upon searching I could not find a resource where they had created the time dimension only using Power Query.

Gilbert’s gives you a key insight into the value of time dimensions: breaking everything out into periods. 5-minute intervals, 15-minute intervals, or however the company looks at data.

Comments closed

Asymmetric Crosstabs in Power BI

Published 2019-08-27 by Kevin Feasel

Teo Lachev shows how we can implement asymmetric crosstabs in Power BI:

The Internet column shows the sales amount from FactInternetSales. Then, the matrix pivots on the BusinessType column in the FactResellerSales. Because, Internet sales don’t relate to BusinessType, it doesn’t make sense to pivot it. Instead, we want to show Internet sales in a single static column before the crosstab portion starts.

Implementing such a report in SSRS is easy thanks to its support of adjacent groups and static columns but not so much in Power BI. The issue is that Matrix would happily pivot both measures and the InternetSalesAmount would be repeated for each business type.

The solution isn’t awful, but it does involve knowledge of DAX.

Comments closed