Automatically ranged date table using DAX and M

I am prepping some demo’s for my SQLPass summit session “End-to-End Demos with Power BI” where I’ll show many of the tricks I use in my Power BI workbooks.

One of them is this trick that I recently figured out, I am so excited about it that I wanted to share it with all of you. I figured out a way to automatically generate a data table with a dynamic range based on the data in the fact tables.

Read more »


Export a table or DAX query from Power Pivot to CSV using VBA

Had an interesting question today. Someone did some great transformations combining multiple files into a single table and adding some calculations using Power Query and Power Pivot. Now he wanted to load that data into CloudML to do some machine learning on top of this data. Currently cloudML takes csv files so the question was how can I get data from the Power Pivot model into a CSV even when there are more than 1.000.000 rows.

It turned out to be pretty easy by combining two other blog posts: one of my blogs on how to access the data model combined with this blog I found on how to write to CSV from vba.
Read more »


Getting data into Power Query with the twitter search API (how to hack PQ to use OAuth)

Last week I decided to try an interesting experiment. Lets see if I can get data from the Twitter REST API into Power Pivot using Power Query. Power Query already supports two out of three things that are needed for me to import this data:

  1. Import from a web url is already supported
  2. Parsing JSON data is already supported
  3. Logging into the service with OAuth is not supported for a datasource that is not developed by the Power Query team. Unfortunately even though most products use OAuth, no OAuth implementation is the same. Thanks to some hacking with Power Query I managed to work around this.

So lets go take a look at how we can solve this.

Read more »


Import data from TFS online into the data model through Power Query

I was playing around today with some TFS data (that’s were we store all our product data Smile) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way Smile

It turns out TFS online supports OData for most of their services. See this blog post on how to enable it and where to connect to: 

The important thing is this:

Team Foundation Service authentication:

(Optional) In order to authenticate with Team Foundation Service, you will need to enable and configure basic auth credentials on

  • Navigate to the account that you want to use on For example, you may have
  • In the top-right corner, click on your account name and then select My Profile
  • Select the Credentials tab
  • Click the ‘Enable alternate credentials and set password‘ link
  • Enter a password. It is suggested that you choose a unique password here (not associated with any other accounts)
  • Click Save Changes

To authenticate against the OData service, you need to send your basic auth credentials in the following domain\username and password format:

  • account\username
  • password
  • Note: account is from, username is from the Credentials tab under My Profile, and password is the password that you just created.

So I did that for my TFS online instance and opened up PQ and selected import from OData:


Next I pasted in my workitems OData url (that I got from the site above):


This prompts me for authentication, and I choose basic auth and enter my credentials:


This opens up the query editor and I am good to go from that point:


It’s pretty cool that this worked and I though I share it Smile


Excel 2013, Power Pivot and Power View on any device with Azure RemoteApp

I just found out about this great new Azure service that allows you to run any app on any device, including several default templates like Excel 2013 Pro plus. So my first thought was of course, cool lets try to run Excel on the IPad we have at home.

First what is Azure Azure RemoteApp, from the website:

Azure RemoteApp helps employees stay productive anywhere, and on a variety of devices – Windows, Mac OS X, iOS, or Android. Your company’s applications run on Windows Server in the Azure cloud, where they’re easier to scale and update. Users can access their applications remotely from their Internet-connected laptop, tablet, or phone. While appearing to run on the users’ local device, the applications are centralized on Azure’s protected, reliable platform.

Azure RemoteApp combines Windows application experiences with the powerful capabilities of Remote Desktop Services on Microsoft Azure – the cloud for modern business.

Read more »


Where your calculated field is executed matters

I had an interesting question on one of my older blog posts Filtering results to only show top x results in Power View chart last week:

Your logic above makes perfect sense, but I can’t crack it for some reason. This is making me Sad!!!! There is obviously something missing in my logic. I have created a measure as follows

=RANKX(ALL(Table2[City]), SUM(Table2[Sales]))

The measure is called Ranking. However when I put this on my powerview chart the only value is 1. I want to sum the data by city and rank by the cities.
This is my data

Country City Sales
Germany Munich 1
Germany T 3
Germany D 2
Germany Munich 7
Germany A 8
Germany Munich 6
Germany P 9

This is actually a topic that I spend quite some time in one of my recent DAX session that I did at the PASS BA conference and TechEd. You can watch the session online right here: Solving Complex Business Problems with DAX

Read more »


Show the sum of sales for the last 3 days based on date selection

A quick post today that came from one of the readers on the ask a question page:

Let’s say we have a dates table and fact table linked by a field called DatesID. The dates table has one field called DatesID and the fact table has two fields (DatesID and Quantity) as follows:

Date, Quantity
5/1/14, 1
5/2/14, 2
5/3/14, 3

We have a measure for CurrentQuantity:=SUM(fact[Quantity]). When we select 5/3/14 on a slicer for dates[DateID], we get 3 from Pivot1. Everything is fine at this point.

Where I run into trouble is with Pivot2 where I want to list all dates within the last 30 days of the slicer date along with the corresponding quantity.

So for example: By selecting 5/3/14 on the date slicer, we want to produce the following on Pivot2:

Date, Quantity
5/1/14, 1
5/2/14, 2
5/3/14, 3

Similarly, when we select 5/2/14 on the dates slicer, we would get 2 from our CurrentQuantity measure and 5/3/14 would drop from pivot2.

Read more »


Dashboarding with Excel and PowerPivot example dataset

I have had some questions about the example dataset that I used in the book and if this was publicly available. For the book I took the MecDatamarket database from the MS BI VPC and modified the data and loaded it into Access. You can download the file here:

All rights of this dataset are reserved to Microsoft.