Press "Enter" to skip to content

Category: Power BI

Modeling Semi-Additive Measures

Published 2019-09-16 by Kevin Feasel

Paul Poco shows a couple techniques for modeling semi-additive measures in Analysis Services and Power BI:

As mentioned earlier, the most commonly encountered approach is Option 2, the snapshot fact table. The main drawback of this approach is that the fact table’s size will grow extremely fast. For example, if you want to calculate the headcount in a company with 10,000 employees on average, and you want 5 years of historical data, you will add 10,000 rows per day to your fact table – that gives you (10,000 * 365 * 5 =) 18,250,000 rows after 5 years.  

If you used the first approach, Option 1, the fact table would be (10,000 * 5 =) 50,000 rows after 5 years, assuming your employees change position or quit the company once a year, on average. 

The snapshot fact table (Option 2) is (18,250,000 / 50,000 =) 365 times bigger. On the bright side, as the data is very repetitive, you might get a very good compression ratio on these tables.  

Check it out. Semi-additive measures are not as common as additive measures, but you’re liable to have a couple of them in your data model.

Comments closed

Prior Year to a Specific Date in DAX

Published 2019-09-13 by Kevin Feasel

Alberto Ferrari lets us compare up to specific dates between years:

Unfortunately, the calculation is not perfect. At the year level, it compares the full previous year against an incomplete current year – in this example there are no sales after September 5th in the current year.

Besides, the problem appears not only at the year level, but also at the month level. Indeed, in September the Previous Year measure returns sales for the entire month of September in the previous year. The comparison is unfair, as there are only five days’ worth of sales in September of the current year.

Read on for a better technique.

Comments closed

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