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.

So I applied for the preview on Azure last week and got access today, it now shows up in my Azure portal:

Clicking the link gives me the overview of my RemoteApp, of course I don’t have anything set up yet:

So I create a new RemoteApp where I select the default template image. This image support Office 2013 Pro Plus out of the box (how convenient ). Of course you can also create your own templates but I won’t go into those details.

After clicking Create it starts the creation process:

and after some time it finished creation and provisioning. Now I can start configuring the app:

When I chose remoteapp programs I will see what apps are installed by default:

Excel is part of it. In the users part I could add additional users. In my case I just added me.

Now to use it you need install an additional application, I am getting started with my windows machine where I downloaded the add through the RemoteApp website:

After it downloaded I need to set it up. All I needed to do was log in with my Microsoft credentials. I now get a list of all my apps that I installed:

After selecting Excel it starts loading Excel (takes a bit longer the first time starting the app on your machine):

Now I have a version of Excel 2013 running on my machine without installing it (I signed into Office with my credentials as well):

The only difference you notice is a different icon in the task bar:

The version that is installed is Pro Plus:

And best news is that the Excel contains all the add-ins:

This means I can open any workbook that contains a model (this is the workbook used for my dashboarding book so it’s a not too shabby workbook):

Interestingly enough the memory on machine didn’t grow at all, so it is loading it on the server:

That also means you cannot open local documents and it is highly recommended to use things like OneDrive / Onedrive Pro or SharePoint

Now the cool thing is that it also supports running the same app on the IPad or any RT device. For IPad you just need to run the latest Remote desktop app. Here are some pictures running Excel on my daughters IPad, again opening the dashboard workbook:

Clicking a slicer updates the numbers:

Power View also works:

Pretty cool right! It also works on OS X and Android, click here for more details.

]]>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 dataCountry 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

Lets take a closer look at this problem, Here I created the table into Excel and loaded it into the model:

I then created the measures as mentioned in the comment above:

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

Now adding city and the measure in the following PivotTable gives us the following results:

So what is going on here? Why do we get 1 for all rows? Lets start by examining the documentation of the RANKX function: “Returns the ranking of a number in a list of numbers for each row in the table argument.” If we then look at the the three arguments (the third argument is optional, when left empty the second argument will be repeated, as is the case with the measure used for our example):

table Any DAX expression that returns a table of data over which the expression is evaluated.

expression Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking. See the remarks section to understand the function behavior when expression evaluates to BLANK.

value (Optional) Any DAX expression that returns a single scalar value whose rank is to be found. See the remarks section to understand the function’s behavior when value is not found in the expression.

When the value parameter is omitted, the value of expression at the current row is used instead.

Now what does that mean? Lets look at what this ranking measure does in the Pivot Table:

Now lets take a look at what is being evaluated for the red highlighted cell inside the engine. The RANKX will compare the measure in the RANKX parameter for each row on the table provided in the first argument to the current cell. What happens is that the measure “SUM(Table1[Sales])” gets executed twice, once to get the value for this measure in the current context (the highlighted cell above) and once for every row in the table supplied with the first argument. This is the part where you start getting unexpected results.

If you visualize what is being calculated here you get the following table, a row for each city in the table and its comparing rank:

As you will see is that each row returns the same results, it gives us values for the “SUM(Table1[Sales])” filtered by the values for the current cell (the outside context) instead of calculating the “SUM(Table1[Sales])” for each row of the table. What is going on here?

What is happening here is actually very similar to creating a calculated column and putting the same measure in that column:

Again what happens is that “SUM(Table1[Sales])” doesn’t reflect the filters for each row but only the filter from the outside context, in case of a calculated column there is no outside context as there is no Pivot Table.

If we want the row context to be applied to the calculation we need to change the measure so the row context will be promoted to filter context, to do this we need to simply wrap it around with a CALCULATE. Instead of writing:

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

you need to write:

=RANKX(ALL(Table1[City]), CALCULATE(SUM(Table1[Sales])))

CALCULATE will do two things:

1 Evaluate the expression with the filters you applied as the arguments of CALCULATE

2 If an expression is calculated in a certain row context, it automatically adds that row context as filter

The second part is what helps us in this case as it will filter the measure with the city for the current row. Let’s take a look at what is going on with the new measure for the highlighted red cell of the Pivot Table above:

It might be easier to understand when thinking about the measure written this way (this is not really what happens). For the first row this is what happens, notice the filter set to City = “A”:

=RANKX(ALL(Table1[City]), CALCULATE(SUM(Table1[Sales]),[Cities]=”A”), CALCULATE(SUM(Table1[Sales])))

Now putting this measure on the Pivot Table you see we get the right results:

The same happens when we use this measure in the calculated column:

As you might notice this is quite a long and complex blog post for such a simple measure but is quite interesting to see what is going on here. Most people will actually never encounter this problem because of a small “hack” the DAX formula engine does when you use a measure instead of a formula. When using a measure the formula engine automatically wraps a CALCULATE around the measure. So instead of:

=RANKX(ALL(Table1[City]), [Sum of Sales]))

we actually do:

=RANKX(ALL(Table1[City]), CALCULATE([Sum of Sales])))

This was you will never see the same behavior when you use a measure instead of writing the actual calculation.

Hope this gave you some insights in the inner workings of DAX.

]]>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, 3We 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, 3Similarly, 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.

So I created two tables that show the above case:

I then loaded them into the model and created a relationship:

Now I created two pivot tables with a slicer of dates in the date table (Table2), the first questions is solved automatically by creating a simple SUM(Table1[Sales]):

Now for the second, I want to see the Sum of Sales for 3 days before and on the current day. This is where some DAX magic comes into play:

=CALCULATE([Sum of Sales],

FILTER(ALL(Table2)

,Table2[Date] <= MAX(Table2[Date]) && Table2[Date] > MAX(Table2[Date])-3

)

)

In this calculation we are to calculate the [Sum of Sales] where we FILTER the results over ALL rows of Table2 (instead of using the date filters on the PivotTable from the slicer), then we return only the [Sum of Sales] for those rows on Table2 where the Date values of Table2 is smaller or equal to MAX Table2[Date] for the current selection (set by the slicer) and Table2[Date] is greater than the MAX of Table2[Date] minus 3 days ago.

Result:

]]>All rights of this dataset are reserved to Microsoft.

]]>

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

As you might see the data we have above is also from the past, I see teams like “west germany”, I want to group the old names with the new names. To do that I created this table:

So I created the following worksheet, I put Home and Away teams on rows. Country from the Team table and Year from the games table as slicers

Now I need to use a DAX expression to show the goals where either the home or away country is equal to the Country selected on the slicer. In order for me to do this I need to be able to compare the two columns that are equal, so I cannot use the team column as that would not compare “West germany” with “Germany”. I do this by adding the Country as a calc colum to the Teams table by using Lookupvalue:

=LOOKUPVALUE(Country[Country], Country[Team], Games[HomeTeam])

This will add the value for Country[Country] to the column where the Country[Team] column is equal to Games[HomeTeam] for the row we are in:

Now I can use a DAX expression to get the Homegoals where the home or away team is from the selection. The below expression gets the SUM of Games[Homegoals] for all the rows in the Games table where the Games[HomeCountry] is the VALUE of the Country[Country] selection OR Games[AwayCountry] is the VALUES of the Country[Country] selection. Make sure to note we use the “Games” table as first argument, that will make sure all the filters, like year, are still applied:

=IF (

HASONEVALUE ( Country[Country] ),

SUMX (

FILTER (

Games,

Games[HomeCountry] = VALUES ( Country[Country] )

|| Games[AwayCountry] = VALUES ( Country[Country] )

),

Games[Homegoals]

),

BLANK ()

)

This gets us the results we want:

Talking to some of my colleagues we decided that there had to be a more optimal way. Taylor my colleague came up with the following, this doesn’t need any of the calc columns. It will calculate the sum of HomeGoals for each row of the filtered Games table where the values of the Games[Hometeam] row CONTAINS the selected (by the slicer) Country[Team] column or the Games[Awayteam] row CONTAINS the selected (by the slicer) Country[Team].

=CALCULATE (

SUM(Games[HomeGoals]),

FILTER (

Games,

CONTAINS ( Country, [Team], Games[Hometeam] )

|| CONTAINS ( Country, [Team], Games[Awayteam] )

)

)

This is a much more elegant solution and also supports selecting multiple countries. This again goes to show that there is no single way of solving any problem using DAX, there is always room to optimize

]]>- 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)

]]>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 . 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**

Date: April 30, 2014

Time: 9:00am–1:00pm

Where: **Live, online virtual classroom**

Cost: **Free!**

I have a model with two tables, one fact table and one data table. They are related through a date field in both tables.

The first thing we do here is create a PivotTable with Fiscal years and Fiscal months on rows. The fields are two columns in my date table:

Now adding the FyYear and FyMonth on rows and the the Sum of Revenue as values:

Now the first thing we need to do is get the sum of revenue for the first month of the fiscal year so that we can compare it with the value of the current month. This actually a combination of three pretty straightforward DAX expressions.

First we are need to be able to find the first day we have sales per year:

First date of the year=

CALCULATE(FIRSTNONBLANK(DateTable[Date]

,[Sum of Revenue])

,ALLEXCEPT(DateTable

,DateTable[FyYear]

)

)

This expression will get the first date in the datetable[date] where there is a value for [Sum of Revenue], then using CALCULATE we will make sure that it calculates that date for all the rows in the DateTable except keeping the filter on DateTable[FyYear]. In short: this will get the first date we have sales for each FiscalYear.

Testing this show it works (remember those are fiscal years on rows):

Now lets go an use this calculation in a calculated column to determine for each year what the first month is for that year:

=IF(

[Year]=YEAR([First date of the year])

&&

[Month]=MONTH([First date of the year])

,1

,0

)

This calculated column will check if the year and month column for the current row this calculated column formula is executed in is the same value as the year and month of the first date of this particular fiscal year:

Now lets calculate the sales amount for the first month of the current fiscal year:

=IF(HASONEVALUE(DateTable[FyYear]),

CALCULATE([Sum of Revenue]

, DateTable[FirstFyMonthofYear]=1

, ALLEXCEPT(DateTable

,DateTable[FyYear])

)

)

This calculation will calculate the sum of revenue where the DateTable[FirstFyMonthofYear]=1 for all rows in the DateTable Except the DateTable[FyYear], meaning it will always keep the filter of DateTable[FyYear] that is created by the row context.

Now for the final calculation we simply compare the two to determine the growth:

=if([Sum of Revenue]>0

&& HASONEVALUE(DateTable[FyMonth])

,DIVIDE([Sum of Revenue]-[Revenue Base Month]

,[Revenue Base Month])

)

This formula check whether [Sum of Revenue] is greater than 0 and a single month is selected for the current cell. When this is true we divide the difference between [Sum of Revenue] and [Revenue Base Month] by [Revenue Base Month] resulting in the % change compared to the base month:

]]>Why does my function DATEADD not return what I expect? It gives me empty results on some row??

So what is happening here? He is using the DATEADD function to move back to the value of Table1[Date] 14 days previous to the value for the current row. Remember DATEADD is a time intelligence function, those have special rules, one of them is that the the dates need to be in a contiguous date range, there cannot be a missing dates in the middle. If you do miss dates you will get unexpected results, in the case above the date “12/7/2013” did not exist as a value in the Table1[Date] column, thus will return empty results.

What do you need to do? two options:

1 Create a separate date table containing a continuous date range for all values you want to report on, this will solve the “missing values”

2 Don’t use time intelligence functions, in this case you could have done a simple “=Table1[Date]-14”. That DAX expression simply uses the value for each row in the table to create a new date value. It doesn’t need to lookup a value in the column, it can work off its own value.

]]>