Press "Enter" to skip to content

Category: Power BI

IIS Log Analysis in Power BI

Published 2019-09-18 by Kevin Feasel

Joy George Kunjikkur shows how you can build a Power BI dashboard to analyze IIS log files:

As developers, we all might have encountered situation of analyzing IIS web server logs. During the development time, the file is small and easy to analyze in Notepad or Excel. But when it grows to GBs in production servers we use other tools. One such popular tool to query IIS logs is LogParser. It is a free command-line tool from Microsoft. There are graphical applications around it to generate even charts. One such free tool is the Log Parser Studio. It is also from Microsoft.


Once we move up in career and had to deal with managers, product stakeholders or ever to CXO to show what the IIS logs say, we need more visuals or a dashboard reflecting the IIS logs. Though we can create visual using Log Parser Studio, it is tedious creating reports and charts one by one. 

Click through for a solution.

Comments closed

Custom Formatting Numbers in Power BI

Published 2019-09-17 by Kevin Feasel

Chris Webb shows how you can use custom formats to display numbers more easily in Power BI:

Now that we can apply custom format strings to fields and measures in Power BI in the September 2019 release, I thought it would be useful to provide some examples of what’s possible with this very flexible new feature because the existing documentation for VBA isn’t easy to make sense of. In fact there’s so much to say I’m going to have to write a series of blog posts to cover everything! In this first post I’m going to look at formatting numbers.

When you need an exact number, a thousands separator goes a long way.

Comments closed

Comparing Power BI Files

Published 2019-09-16 by Kevin Feasel

Imke Feldmann shows off a new Power BI file comparison tool:

What’s not covered?
Nothing. The comparison includes everything from the pbit-files: So beneath your M and DAX code, you’ll see all about your visual definitions (incl. filters set !), row level security and much, much more. Actually, I found some information a bit noisy (like many date fields, telling you when which changes happened). So I filtered them out in Excel. I’d recommend to check it out and play a bit with it to find the most suitable settings for you.

This looks quite useful.

Comments closed

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