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: http://1drv.ms/1miiVPG

All rights of this dataset are reserved to Microsoft.



Show the goals when your team is either away or home

I got an interesting question last night in preparation for the world cup. How can I get a PivotTable where I can see the results where the team selected in a slicer is either the Home or the Away team. Because we need to do show games where the Home or the Away team are selected here we cannot use traditional relationships.

This is the table of games I have (yes its fake, I made it myself before you wonder if these games are actually played):


Read more »


TechEd houston update

Last week I was at the PassBA conference in San Jose and it was great meeting a lot of you. This week I will be at TechEd in Houston where I hope to chat with even more of you. You can find me at the following places:

  • Monday night reception at the BI booth at the expo
  • Tuesday at 12:15 to 2:30 at the BI booth at the expo
  • Tuesday 5pm at Power hour
  • Tuesday night, ask the experts
  • Wednesdays 1:30 my deep dive into DAX session
  • Thursday 8:30 my session on DAX over Multidimensional
  • Thursday 10:45 at the BI booth at the expo.


TechEd will also be the first place where you can get a copy of the book, very exciting (for me)



Book update and upcoming conferences

Today I received my copy of my Dashboarding for Power Pivot and Excel book directly from the printer:

WP_20140501_17_45_02_Pro 2

Really exciting to be able to hold this in my hands! It was shipped to me at the same time as it was to the distributor, from there it goes to Amazon. We expect Amazon to have it available and start sending out preorders in two to three weeks. So we are getting close!

I will also be at several conferences in the upcoming weeks. First I will be at PASS BA next week with a level 400 session on DAX. I will be handing out some books at the session Smile. Then I will be at TechEd in Houston with the same DAX session and a session Power View for Multidimensional (Also handing out some copies here). Thanks to some smart logistics the book will be for sale at the TechEd bookstore, so the first real chance to get the book.

I hope to see many of you at PASS BA or TechEd, I will be manning the booth a lot so feel free to drop by for a chat!


Microsoft Corporate BI and SAP BI Integration Jump Start

Tomorrow I will be presenting at the Microsoft Corporate BI and SAP BI Integration Jump Start MVA, MVA stands for Microsoft Virtual Academy and is a website for Developers and IT Pros offering free Microsoft training delivered by experts. I will talk about corporate BI and how the IT team can help get more power into the hands of the business. The best thing about it is that is free! Sign up now:


Microsoft Corporate BI and SAP BI Integration Jump Start

Date:  April 30, 2014
Time: 9:00am–1:00pm
Where: Live, online virtual classroom
Cost: Free!

Register now