PowerPivot Posts

The ALL() Function in DAX

Fri, 14 Jul 2017 18:05:35 +0000

In this article I am going to write about the ALL() function, show how it works and explain a common use case. I will also cover the unexpected behaviour that occurs when using the ALL() function in Power BI Desktop – more on that later.

For this article I will use my simplified copy of the Adventure Works database as shown below.

Power Pivot Crashes–Another Update from Seattle

Fri, 23 Jun 2017 12:33:46 +0000

When I was at the Microsoft Data Insights Summit last week I took the opportunity to catch up with Howie Dickerman to get an update on how the bug fixing with Power Pivot for Excel is coming along. I talked about this in my original blog here and then my previous update here. And the good news is the work is going very well. The summary of what Howie told me is as follows:

The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

Wed, 22 Mar 2017 18:37:43 +0000

Quick post today because, well, even more going on than usual.

This is actually the fifth post on this specific topic, which means that it’s something that keeps coming up. But unlike previous posts, in which we kept DISCOVERING slightly better ways to do things, this latest post is triggered by us GETTING a new way to do it from Microsoft.

Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

Wed, 22 Mar 2017 18:33:05 +0000

Typical Client Report Requests

Simple but complex
High-level yet detailed
Compact yet containing everything…

Quite understandably, clients love to channel their inner Marie Antoinette by basically asking to have their cake and eat it too. I actually relish those scenarios, they allow me to flex my “think outside the box” muscles! And hey, it’s great to be working with a toolset that truly CAN accommodate the special demands posed by real-world situations.

5 Useful Data Analysis Expressions (DAX) Functions for Beginners

Wed, 08 Mar 2017 20:27:41 +0000

Getting started with Data Analysis Expressions (DAX) can be intimidating, but becoming knowledgeable in just a few basic functions can help you to unlock many new insights into your data. While it is easy to create visuals in Power BI or Pivot Charts, we often desire to view data for specific time frames, or with specific filters applied or removed, that we are unable to accomplish with our raw data.

Handling Missing Members In The CubeSet() Function With Power Pivot

Thu, 23 Feb 2017 19:58:55 +0000

Reposted from Chris Webb's blog with the author's permission.

Last week I received an email from a reader asking how to handle missing members in MDX used in the Excel CubeSet() function. My first thought was that this could be solved easily with the MDXMissingMemberMode connection string property but it turns out this can’t be used with Power Pivot in Excel 2013/6 because you can’t edit the connection string back to the Excel Data Model:

Compare equivalent periods in DAX

Fri, 20 Jan 2017 20:10:11 +0000

If you do not have a full month with data, comparisons such as year-over-year (YOY) might require particular filters in order to do a like for like comparison in DAX. This article describes how to write DAX expressions comparing equivalent periods, keeping corresponding number of days in the comparison.

When you create a year-over-year in DAX, you usually compare two set of dates from the calendar, regardless of the presence of data in all the days of the period. For example, consider the following year-over-year (YOY) calculation for Sales in December 2008 for a particular store.

DAX Time Intelligence for Beginners

Mon, 16 Jan 2017 20:12:55 +0000

I help a lot of people on forums who ask questions about time intelligence for DAX. If you are just starting out then the chances are that you may not even be clear what time intelligence is and hence sometimes you don’t even know what to ask. Often the question is something like “I want to show this year and last year on a chart”, or “total year to date this year compared with last year” etc. If you want to do any time based comparison using DAX, Power Pivot and or Power BI, then this article explaining time intelligence is the right article for you.

How Many Working Days Has An Employee Been Off Work?

Fri, 16 Dec 2016 20:36:54 +0000

I answered some forum questions recently that were similar in nature and I thought they would make a good blog article. The scenario is this: there is a table that contains information about when an employee was absent from work like shown below.

The question is “How many working days were they off work giving consideration to both weekends and public holidays?”.

The trick to solving this problem is to create a good quality calendar table and use the calendar table to validate the days off. I explain the process below.

How to Document DAX Measures in Excel

Thu, 08 Dec 2016 14:32:12 +0000

I often get asked if there is an easy way to create documentation for DAX measures when using Power Pivot for Excel. I am not a big fan of documentation for the sake of it, but I do see value in having “some” appropriate level of documentation. I think a good balance of the right amount of documentation vs the effort to write and maintain the documentation is to use the Power Pivot UI itself. I explain how I do it below.