Image we have the following Products with revenue for the following fiscal year and month. This data comes from your nicely structured datawarehouse:

Your managers, who asked for this report, wants to see these values combined with the target values. You get these values delivered in the following pivoted text file:

The problem with a format like this is that it is not easy to pivot on. In order for us to to do analytics on top of this we need to reshape the data into a shape where the months and years become a value, not a header. This is called Unpivotting.

So how do I do this ? And how do you get these numbers into one combined  Pivottable ? The answer here is Data Explorer. I select import from CSV in DataExplorer and point to the CSV file.

First I double click on Query and rename the query to revenueTarget to give the query a proper name, next I select that I want the First row as headers:

select all the columns that contain values and right mouse click, select Unpivot columns:

This will give us the result we want where year and month have now become values instead of column headers:

Now I rename columns to something sensible. In my PowerPivot model I need to combine this data with a date column so I would like to add that as well. I right mouse click on the date column, press Insert Column and select Custom

Here I can enter a value that will create a new date field:

=Text.End([YearMonth],2) & “/1/” &Text.Start([YearMonth],4)

And I Change the type to Date:

Ok the transformations are completed. Press Done and Add the results to the data model:

Now to add this to the data model I need d to create relationships, I do this by creating relationships to the DateTable and Product. I am essentially treating this new table a secondary fact table:

Now to combine values from the Invoice table and RevenueTarget into one KPI I go back to the datagrid view, select the RevenueTarget column and click autosum on the ribbon to create a measure for Sum of RevenueTarget. I also create a measure for that will serve as the base measure for our KPI. I add the following measure:

KPI:=[Sum of RevenueAmount]

Now I right mouse click and select KPI, I select the Sum of RevenueTarget as target value:

Now adding this KPI to the Pivottable we had in the beginning and voila:

I imported the data but it took over one hour to import the data. I was very unsatisfied with the time it took me to refresh the data. Then I got thinking, MDX is not really designed for these tabular result sets, DAX is. As this is a tabular model I can use DAX to get the results. Unfortunately I don’t have a automated DAX query editor but writing the DAX equivalent of this particular MDX query is quite simple. After rewriting the query to DAX is brought back the import time to …. 3 minutes. From one hour on the same dataset.

These are the steps needed to import the data, to start you get this empty window that asks you to specify the MDX query:

Now instead of writing the MDX you can click on Design to have the MDX query designer help you build it:

Now press OK or click on the designer Icon in the top right to get to see the MDX. The query builder will generate the following MDX:

SELECT NON EMPTY { [Measures].[Sum of SalesAmount] } ON COLUMNS,

NON EMPTY { ([DateTable].[FullDateAlternateKey].[FullDateAlternateKey].ALLMEMBERS *

[Product].[Color].[Color].ALLMEMBERS *

[Product].[ModelName].[ModelName].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( { [DateTable].[CalendarYear].&[2004] } )

ON COLUMNS FROM [Model])

WHERE ( [DateTable].[CalendarYear].&[2004] )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Now rewriting this to DAX, interesting to note is that DAX works inside out, start by reading the comments/functions from the most inner function and work your way to the outside to see what the query returns:

EVALUATE
//Calculate the table to filter the results by the CalendarYear 2004
CALCULATETABLE(
//Add columns to table containing the measures
ADDCOLUMNS(
//Filter the results where we actualy have a measure
FILTER(
//Crossjoin all the columns required in the resultset
CROSSJOIN(
values(DateTable[FullDateAlternateKey]),
//when multiple columns from same table add them together with
//Summarize for performance
Summarize(values(Product),
Product[color],
Product[ModelName])
)
, Not IsBlank(Sales[Sum of SalesAmount])
)
, “[Total]“, Sales[Sum of SalesAmount]
)
, DateTable[calendaryear] = 2004
)

The above query is also a general approach on how you should write a query to retrieve a tabular result set using DAX to get the fastest result. It should work in most circumstances.

I am going to load the csv file into Excel using DataExplorer (download add-in for Excel 2012 here).

And I select Import from Csv, and select the file that I received. That opens the DataExplorer query editor and in this case I want to import the csv file completely without any changes. I do want to Use the first Row as Headers :

Now I import this data into Excel,

after importing the data into Excel I want to rename my query by double clicking Query1 and rename it to “Twitter Data” and press Load to data model to load it into the AS engine.

Now I want to plot the tweets on a map. I am going to use GeoFlow for that, as this is data that I got from the Internet there are some data quality issues. The current version of GeoFlow doesn’t  allow us to filter data. So I am going to use a Excel trick to create a filtered down table by using a Query Table where I filter out all tweets where no region is defined.

So we go back to Excel, select an empty sheet and click on Data, Existing Connections, Tables and double click the Twitter data table:

And press OK in the next dialog to add a table. This adds a table to the Excel sheet:

Now right mouse click on the table and select Table, Edit DAX:

Now I change what is shown on the sheet by editing the DAX expression for this table:

Evaluate
calculatetable(
Summarize(
‘Twitter data’,
‘Twitter data’[ID]
,’Twitter data’[Location]
)
, ISBLANK(‘Twitter data’[Location]) = false)

Press OK and this will only give me that ID’s and Locations where the Location is not blank:

Comment: the results of the query will have the limitation of 1,000,000 rows in Excel but you could always aggregate the results by location using DAX. I don’t think many people want to plot more than a 1,000,000 locations. 

Now go to Insert GeoFlow (Install add-in here):

In GeoFlow you now can pick columns from the filtered table:

Now select location as other and Map it:

Now select ID as height:

This plots all the tweets on Azure for the last week on the map, you might notice the Netherlands and Belgium are quite some hotspots on Azure (no I did not rig the results!)

Hope that you some idea’s on how to use Excel, DataExplorer and GeoFlow.

Kasper

First I want to import the data that I need. I am going to use Data Explorer to import the data into Excel and then I am going to use PowerPivot to analyze the results.

First data source that I am going to use is http://espn.go.com/nba/salaries/_/page/1

this I want to combine with http://espn.go.com/nba/standings/_/group/1

I start by importing the standings, go to Excel and the Data Explorer tab and select “from web”

Paste in the URL and select Table 0

Now I want to remove the first row so I can select Remove Top Rows and select the first row and I also select Use first Row as Headers:

Now I am only interested in the Team name and win – loss pct so I am removing all other rows by selecting Hide Columns:”

Now I want to clean up the Team name to remove unwanted characters:

I select Replace Values and remove y – and x- with an empty value.

Ok I am happy with the result and I press Done to import the data into Excel:

I want to rename the Query to NBA Standings and Load it to the Data model.

Next up is import a list of all the players and their Salaries. Again the same thing Import from web, this time as is:

I press OK to import and rename the Table:

Now we only see 40 players here as this webpage we import from actually has multiple pages. The cool thing about Data Explorer is that under the covers its is a special language called M. What we are going to do now is make a function out of the import function that we can call iteratively for each page.

First I need to make sure I can actually edit queries, I go to the Data Explorer tab and select “Enable Advanced Query Editing”:

Now I go back to the table I just imported and click on “Filter & Shape” and click on this little “script” icon that now appears:”

This pops open the script dialog:

,

I change the script to the following:

(page) => let
Source = Web.Page(Web.Contents(“http://espn.go.com/nba/salaries/_/page/” & Number.ToText(page))),
Data0 = Source{0}[Data]
in
Data0

This will add a empty sheet to excel with just this function, nothing can be shown on the table but it needs to be there, I also renamed the table to Query.

Ok now I want to use that function for all 14 pages. I go to Data Explorer and select import from other sources, “Write Query”.

This opens a blank query window:

Here I paste in the following:

let
Source = Table.Combine(List.Transform({1..14}, Query))
in
Source

this will combine the results of a the query function executed from 1 to 14

Press OK and the all data (527 rows instead of 40) will be added to the sheet:

Pretty awesome right ? Now lets add this to the model as well by clicking load to data model:

Quick tip, don’t rename the tables. This doesn’t work in the current build of Data Explore and will screw up the connection to the model.

Now I want to create a relationship but we have problem, how do I relate:

with

We need to make the values in the column the same. I will plan to extract the city from team in dataset 2 to combine it with dataset 1 and remove LA from dataset1.

So I click on filter and reshape again at the salaries. I select the column and select by delimiter, select space and at the right-most delimiter.

That will give me:

Now I rename the Team.1 to City and Team.2 to Team. The last problem is that LA has two teams. You can see that in the first dataset they solved that by deviating from the norm by adding LA Lakers and LA Clippers. I need to do the same here.

First I rename Los Angeles to LA. Now I add a new column that based on my expression add the team name if the city is Lakers. I do right mouse click on the Salary column and select Insert column, Custom… This opens a new window where I can type a expression. What I added was:

if [City]=”LA” then [City] & ” ” & [TEAM] else [City]

This will give me the result that I want (I also renamed the columns):

Except that the steps did not anticipate a NBA team with two words “Trail Blazers” . So I added a special step for that to the formula”:

if [City]=”LA” then [City] & ” ” & [TEAM] else if [City]=”Portland Trail” then “Portland” else [City]

I press OK and reimport the data.

Now I am able to create a relationship in PowerPivot:

and start my Analysis by creating a Pivottable. I also formatted PCT and Salary in PowerPivot to get a better view.

There is a large amount of players in there that are not assigned to any team. I want to filter those out of the result set. So I go back to Data Explorer and deselect players with an empty city:

This gets rid of 100+ players that I don’t care about.

Now lets look at the data and add a measure that determine the PCT of Salary compared to all other teams.

I add the following measure to PowerPivot:

Salary all teams:=CALCULATE(sum(Query1[SALARY]),ALL(Query))

The first shocking result was when this measure returned the value of 1.8 billion ..:

Now I add a percentage of salary for each team against all salaries.

pct Salary:=sum(Query1[SALARY])/[Salary all teams]

To make it a little bit more clear instead of PCT I also create a rank:

Rank by PCT of winnings:=RANKX(all(Query[NBA]),[Sum of PCT])

this really gives you an idea that money doesn’t buy you everything:

The most interesting one is Indiana all the way down with almost half the salary costs as the Lakers at 7th best team in the NBA:

and the teams above and below the Lakers earn less together  then the Lakers themselves..

Next up I would like to add a measure that will calculate the salary per player. So add the following two measures:

nr of players:=COUNTROWS(Query1)

Salary per player:=sum(Query1[SALARY]) / [nr of players]

Unfortunately that didn’t give us much interesting information, besides what we already know. It is interesting to see that a few teams have almost 20 players on their team:

While Chicago and the Lakers are not even capable of doing 5 on 5 in training ..

Hope that gives you some idea of the power of Data Explorer and PowerPivot together.

But I recently found a new way that would allow me to get this information in PowerPivot / Excel 2013 using a macro. Many thanks to Ken Puls  who helped me clean up the macro and give me some great Excel tips. It always great to see SQL and Excel folks learn things from each other . Thanks to his help this macro, once created, will be accessible for all workbooks without having to change the individual workbooks.

This macro uses the Excel connection retrieved from the Excel OM and sends a SSAS DMV query to the embedded model to retrieve memory information. Using the connection to send custom query is not a scenario that is supported, meaning custom queries send through the connection will not be guaranteed to work in any future updates of the product.

When you run this macro in any workbook that contains a Excel Data Model will give you a new worksheet with a table containing all tables, columns, datatype and MemorySize (KB). It also has a Pivottable that aggregates the results and is sorted by size:

The SSAS DMV used to get this information does only return the segment size of a column, this is not all the memory that is used per column but will give enough information to get an good idea of the actual usage. For more details on the internals of the Tabular engine please watch this teched presentation

To get this Macro available for all you workbooks in Excel the following steps are needed.

• If you haven’t already exposed the Developer tab in Excel, do that first. (right mouse click on the ribbon)
• On the developer tab, record a new macro, and choose to store it in your “Personal Workbook”
• Stop recording
• Press Alt+F11 to get into the Visual basic editor
• Find Module1 in the personal macro workbook
• Replace ALL the code for the code you just recorded in there with the code in the attached file
• Go back to Excel
• Press Alt + F8 and run the Macro

The entire Macro is as following:

Option Explicit

Sub GetMemoryUsage()
    Dim wbTarget As Workbook
    Dim ws As Worksheet
    Dim rs As Object
    Dim lRows As Long
    Dim lRow As Long
    Dim sReportName As String
    Dim sQuery As String
    sReportName = "Memory_Usage"

    'Suppress alerts and screen updates
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    'Bind to active workbook
    Set wbTarget = ActiveWorkbook

    'Check if a worksheet already exists
    Err.Clear
    On Error Resume Next
    Set ws = wbTarget.Worksheets(sReportName)
    If Err.Number = 0 Then
        'Worksheet found
        If MsgBox("A memory usage sheet workbook is already detected, " & _
            "do you want to remove the existing one and continue?", vbYesNo) = vbYes Then
                ws.Delete
        Else
            GoTo ExitPoint
        End If
    End If

    On Error GoTo ErrHandler

    'Make sure the model is loaded
    wbTarget.Model.Initialize

    'Send query to the model
    sQuery = "SELECT dimension_name, attribute_name, DataType,(dictionary_size/1024) AS dictionary_size " & _
        "FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS " & _
        "WHERE dictionary_size > 0"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
    lRow = rs.RecordCount

    If lRow > 0 Then
        'Add report worksheet
        Set ws = wbTarget.Worksheets.Add
        With ws
            .Name = sReportName
            .Range("A1").FormulaR1C1 = "Table"
            .Range("B1").FormulaR1C1 = "Column"
            .Range("C1").FormulaR1C1 = "DataType"
            .Range("D1").FormulaR1C1 = "MemorySize (KB)"

            lRows = 2
            rs.MoveFirst

            Do While Not rs.EOF
                'Add the data to the rows
                .Range("A" & lRows).FormulaR1C1 = rs("dimension_name")
                .Range("B" & lRows).FormulaR1C1 = rs("attribute_name")
                .Range("C" & lRows).FormulaR1C1 = rs("DataType")
                .Range("D" & lRows).FormulaR1C1 = rs("dictionary_size")
                lRows = lRows + 1
                rs.movenext
            Loop

            'Format the Memory Size field
            .Columns("D:D").NumberFormat = "#,##0.00"

            'Create table
            .ListObjects.Add(xlSrcRange, .Range("$A$1:$D$" & lRow + 1), , xlYes).Name = "MemorySizeTable"
        End With

        'Create PivotTable
        wbTarget.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="MemorySizeTable", _
            Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Memory_Usage!R2C7", _
            TableName:="MemoryTable", _
            DefaultVersion:=xlPivotTableVersion15

        'Modify the PivotTable
        With ws
            With .PivotTables("MemoryTable")
                With .PivotFields("Table")
                    .Orientation = xlRowField
                    .Position = 1
                    .AutoSort xlDescending, "Sum of MemorySize (KB)"
                End With
                With .PivotFields("Column")
                    .Orientation = xlRowField
                    .Position = 2
                    .AutoSort xlDescending, "Sum of MemorySize (KB)"
                End With
                .AddDataField .PivotFields("MemorySize (KB)"), "Sum of MemorySize (KB)", xlSum
                .PivotFields("Table").AutoSort xlDescending, "Sum of MemorySize (KB)"
                .PivotFields("Column").AutoSort xlDescending, "Sum of MemorySize (KB)"
             End With

            'Format the Memory Size field in the PivotTable
            .Columns("H:H").NumberFormat = "#,##0.00"

            'Add conditional formatting
            With .Range("H3")
                .FormatConditions.AddDatabar
                .FormatConditions(.FormatConditions.Count).ShowValue = True
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1)
                    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
                    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
                    With .BarColor
                        .Color = 13012579
                        .TintAndShade = 0
                    End With
                    .BarFillType = xlDataBarFillGradient
                    .Direction = xlContext
                    .NegativeBarFormat.ColorType = xlDataBarColor
                    .BarBorder.Type = xlDataBarBorderSolid
                    .NegativeBarFormat.BorderColorType = xlDataBarColor
                    With .BarBorder.Color
                        .Color = 13012579
                        .TintAndShade = 0
                    End With
                    .AxisPosition = xlDataBarAxisAutomatic
                    With .AxisColor
                        .Color = 0
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.Color
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.BorderColor
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    .ScopeType = xlSelectionScope
                    .ScopeType = xlFieldsScope
                End With
            End With

            With .Range("H4")
                .FormatConditions.AddDatabar
                .FormatConditions(.FormatConditions.Count).ShowValue = True
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1)
                    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
                    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
                    With .BarColor
                        .Color = 15698432
                        .TintAndShade = 0
                    End With
                    .BarFillType = xlDataBarFillGradient
                    .Direction = xlContext
                    .NegativeBarFormat.ColorType = xlDataBarColor
                    .BarBorder.Type = xlDataBarBorderSolid
                    .NegativeBarFormat.BorderColorType = _
                        xlDataBarColor
                    With .BarBorder.Color
                        .Color = 15698432
                        .TintAndShade = 0
                    End With
                    .AxisPosition = xlDataBarAxisAutomatic
                    With .AxisColor
                        .Color = 0
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.Color
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.BorderColor
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    .ScopeType = xlSelectionScope
                    .ScopeType = xlFieldsScope
                End With
            End With

            'Collapse the PivotTable
            .PivotTables("MemoryTable").PivotFields("Table").ShowDetail = False

            'Set selection to top
            .Range("MemorySizeTable[[#Headers],[Table]]").Select
        End With
    Else
        MsgBox "No model available", vbOKOnly
    End If
    rs.Close

ExitPoint:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    Set rs = Nothing
    Exit Sub

ErrHandler:
    MsgBox "An error occured - " & Err.Description, vbOKOnly
    Resume ExitPoint
End Sub

Download a sample workbook here. The Macro is applied to this workbook only for you to be able to download it, to get this available for all workbooks go through the steps above.

Ok I created a simple Tabular model in SSDT:

The model does not contain any measures, just tables and relationships and marked the date table. I didn’t want to complicate the Tabular model by adding measures specific for a single project.

From this Tabular model I want to create a SSRS report that allows:

• Selection of year, default select the last year we have data (what defines the current year ? )
• Select a product color

And shows:

• Sales per product for the selected year
• Sales per product for the previous year
• Sales per product YoY
• Sales per product YoY %
• Sales YoY product rank

Ok lets get started. In SSDT where created the Tabular Project I added the Report project to the same solution.

As Datasource for my RS project I used the workspace database that I have set up for my tabular model:

Next I created a new report and followed the wizard until I got to the Query Builder:

Press the Query Builder button and click on the DMX data mining button and switch to manual query mode:

The reason that we need to switch to DMX data mining mode is that the designer tries to parse the MDX, and as we are going to write DAX the parser will not understand. Marco explained this bit very well.

Next I paste in a simple DAX query expression to get through the wizard, it doesn’t take well to complex DAX:

Finish the wizard and run the report:

Now we have our first DAX based report.

Ok now lets add the parameters. I usually create shared datasets for these. So I create one for years:

evaluate
SUMMARIZE
(
FactInternetSales,
DimDate[CalendarYear]
)

order by DimDate[CalendarYear]

One to get the last year we have sales

EVALUATE
TOPN(1,
SUMMARIZE
(
FactInternetSales,
DimDate[CalendarYear]
),
CALCULATE(MAX(DimDate[CalendarYear])))

and one for Product Color (we will change this one later)

EVALUATE
SUMMARIZE
(
FactInternetSales,
DimProduct[Color]
)
ORDER BY DimProduct[Color]

What you might notice here is that I use Summarize, this will make sure it only return values that are used in a relationship with the FactInternetSales table. I also order the outcome to make sure we get the “right” order in the report.

Add all the parameters to the report:

This will add the parameters over my report:

Now for the main report I need a more extensive query that allows filtering by parameters and creates some interesting measures on the fly. This is the measure that I created for this purpose:

DEFINE MEASURE FactInternetSales[SumofSales] = SUM(FactInternetSales[SalesAmount])
MEASURE FactInternetSales[SumofSales PY] = CALCULATE(FactInternetSales[SumofSales], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
MEASURE FactInternetSales[SumofSales YoY] = FactInternetSales[SumofSales] – FactInternetSales[SumofSales PY]
MEASURE FactInternetSales[SumofSales YoY pct] = FactInternetSales[SumofSales]  / FactInternetSales[SumofSales PY]

EVALUATE
CALCULATETABLE
(
ADDCOLUMNS
(
FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
),
“Sum of Sales”,FactInternetSales[SumofSales],
“Sales last year”,FactInternetSales[SumofSales PY],
“Sales YoY”,FactInternetSales[SumofSales YoY],
“Sales YoY pct”,FactInternetSales[SumofSales YoY pct],
“Rank”, RANKX(FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)
,FactInternetSales[SumofSales YoY])
),
DimDate[CalendarYear] = 2003,
DimProduct[Color] = “Red”
)
ORDER BY [Rank]

Lets take this step by step. First I create the measures in the define statement, these are pretty straightforward (except the RANK, I will come to that in a bit):

DEFINE MEASURE FactInternetSales[SumofSales] = SUM(FactInternetSales[SalesAmount])
MEASURE FactInternetSales[SumofSales PY] = CALCULATE(FactInternetSales[SumofSales], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
MEASURE FactInternetSales[SumofSales YoY] = FactInternetSales[SumofSales] – FactInternetSales[SumofSales PY]
MEASURE FactInternetSales[SumofSales YoY pct] = FactInternetSales[SumofSales]  / FactInternetSales[SumofSales PY]

Next I start with the Evaluate to return a tabular result and order the result set by Rank:

ORDER BY [Rank]

Then I add CALCULATETABLE that will make sure the returned table will be filtered by

DimDate[CalendarYear] = 2003,
DimProduct[Color] = “Red”

These will be parameterized later.

The core of the query is result set of a CROSSJOIN between CalendarYear and  EnglishProductName. The results are FILTERed where the crossjoin combination has make any sales.

FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)

to this CalendarYear and  EnglishProductName combination that have sales we add some columns

ADDCOLUMNS
(
FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
),
“Sum of Sales”,FactInternetSales[SumofSales],
“Sales last year”,FactInternetSales[SumofSales PY],
“Sales YoY”,FactInternetSales[SumofSales YoY],
“Sales YoY pct”,FactInternetSales[SumofSales YoY pct],
“Rank”, RANKX(FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)
,FactInternetSales[SumofSales YoY])
)

Interestingly in order for us to get RANK properly evaluated we couldn’t use the measure as that would push the calculation to outside the crossjoin calculation filter context. Adding it inline with the addcolumns allowed the filter context to be properly evaluated.

Executing this in SSMS gives us the following table:

Now I went back to RS and opened the dataset properties and pasted the above query into the query field:

Now this doesn’t refresh the fields, so you need to work around this again. Right mouse click on the query, this opens the query but with an error message cannot find cube. Press OK and select the “Model” cube. Now click on the DM icon again, say yes to result loss message and turn off query UI mode (yes its cumbersome) and paste in the query:

Press OK and now you will see the Dataset has been updated with the right fields:

Now you can create the report you want:

And run it:

As you can see I added some expressions to make it look pretty . Now for the last step we need to have the query respect the parameters.

Select the dataset and as you could see in Marco’s blog post we need to change the query into an expression. Go back to the dataset properties:

And click on the formula button. Now instead of a plain string we need to make it a VB like string:

Make sure lines are ended with vbCrLf (A big thanks to Jason Tremper from the RS team that helped me figure this out).

In the bottom part replace the hard coded parameters values with the parameter values:

Update: both Chris Webb and Greg Galloway pointed out that I should be able to use parameters directly in the dataset. I tried that before and got a error message. I tried again today and it worked so I must have done something wrong last time. So here is the preferred alternative to the method above.

Open the dataset properties and select parameters. Add both parameters:

Now  go back to the query and just past the entire query in, as is. No need to make it into a string as I had to do above:

Now for the last part, replace the hardcoded strings with the parameters:

And voila that is all there is to it.

I also applied the same logic to the Color parameter where I wanted to make sure I only show the colors that have sales in the same year:

Adding it all together you get this report:

I think this is a very valuable way of creating reports, I wish I had this when I still was a consultant. Its pretty easy to template and use over time and time again without having to update and change your model. I feel that it also is a little easier this way than using MDX, although it might have to do with a personal preference.

You can download the project here. Its based on Adventureworks.

A question came up recently on how can I limit the number of elements on a chart in Power View by a custom calculation. Imagine you have a chart in Power View with too many data points to make anything out and you want to filter out the “noise”. Just give me the most profitable datapoints.

I created this chart with just a few datapoints. I love how Power View and Bing maps allow me to put any location in and it just knows where to plot it !.

Then I create a chart:

Now I create a DAX measure that determines the Rank of each postalcode by sum of data:

=RANKX(ALL(CityData[PostalCode]),[Sum of Data])

This measure will compare the result of [Sum of Data] for the current data point against the result of [Sum of Data] for all rows in ALL(CityData[PostalCode]) and return the relative rank of those two numbers.

In a table this shows us as following:

Now I put that measure in the map filter area and start filtering on it. This gives us only the top 2 ranked cities:

The rank is automatically measure calculated for each data point in the chart (like what you see in the table in the previous image).

Download the sample workbook here.

You can download it here: http://msdn.microsoft.com/en-us/library/jj874401.aspx

The question was:

DESIRED OUTPUT: Pivot table showing count of asset available by Month; Column Headings = Months & Year; Row Headings = Class.

TABLE1: Includes a list of assets and corresponding Class, Acquired date, and Disposed date.

Asset,Class,Acquired,Disposed
A,TEL,2/16/2010,10/06/2011
B,LBH,3/20/2010,05/23/2012
C,RBH,4/11/2010,06/22/2011
D,TRJ,1/17/2011,09/05/2012
E,DGH,5/9/2011,05/02/2012
F,DGH,7/18/2011,03/13/2012
G,RBH,9/23/2011,01/21/2012
H,TRJ,5/28/2012,06/07/2012
I,THG,7/2/2012,10/11/2012
J,TBD,8/6/2012,12/02/2012

TABLE2: Date Table including fields for Date, Month, and Year

EXAMPLE OUTPUT: Based on the data above, the pivot table output for June-August of 2011 would be as follows…

Year: 2011
Month: Jun, Jul, Aug
DGH, 1.0, 2.0, 2.0
LBH, 1.0, 1.0, 1.0
RBH, 1.0, 0.0, 0.0
TBD, 0.0, 0.0, 0.0
TEL, 1.0, 1.0, 1.0
THG, 0.0, 0.0, 0.0
TRJ, 1.0, 1.0, 1.0

Ok lets solve this using DAX.

First I load the table into Excel 2013, make a table out of it and push it to the Excel data model.

Observe that I changed the dates to have all dates be in 2011 to make it easier for us to debug.

Next I created a date table in Excel:

Back in Excel I create the pivottable like requested in the question:

First things first, I would like to make sure to sort the months in its natural order. We can do that using the PowerPivot add-in. I created a calculated column that determines the month number:

=MONTH([Date])

I then hide the column and use the sort by other column button to tell the Data model to sort Month by Monthnumber

This results in the months sorted “right” in the pivottable:

Now back to the real problem. Notice that I have NOT created a relationship between the two tables.

First thing that I want to do is create a measure that counts the number of facts

Nr facts:=COUNTROWS(Facts)

In the result below this does the dates are not taken into account, but its takes care of counting the facts for the relationships that are there, like nr of products per class:

We are going to use DAX for that as we don’t have a relationship type that allows us to do a “between” function. The DAX function I created will look at each row from the fact table for each product where the product is active in the current month (acquired before or in this month and disposed after this month) based on the current row and column in Pivottable.

The DAX I wrote to do is the following:

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])

))

What does this function do ? First it will execute this function in each and every cell of this pivottable, lets take the first cell:

The values in this cell will be filtered to Fact[class] = “DGH” and Date[Month] =”Jan” based on the values on rows and column for this cell, the “Fact” table that will thus be filtered by Fact[class]:

Hence you can see the result of the measure “Nr facts:=COUNTROWS(Facts)” to be 2 in our pivottable image a few images up.

The date table is filtered by the Date[Month] column that is on rows:

That does not change the value of the measure in our pivottable as there are no relationships between “Date” and “Fact”

Now to make sure that we can also filter the Fact table by the dates we extend our DAX formula to do the following: Calculate the “NR Facts measure” where the “Fact” table (already automatically filtered by Class)  has Facts[Acquired] before the current selected Dates[Date] or Facts[Disposed] after the current selected Dates[Date]:

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])

))

Because the Date table is filtered by the month that we have on columns we get a single date for each cell that we can use in our filter (as each month only has one date in our example). Just to be sure the formula also works when more then one date is selected I use MAX and MIN instead of values, that way we can easily switch to different granularities if we want to later.

There is one flaw of this approach. The Dates[Date] column right now is the beginning of the month, that will never give results for the entire month. So I added a calculated column to the Date table that give me the end date of the current month:

=EOMONTH([Date],0)

Now to use it in the function

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[EoMonthDate])
&& Facts[Disposed] >= MIN(Dates[Date])

))

This will give me the number of ”Facts” grouped by Month and Class:

I can now swap month for year and still get the right results (because of using MAX / MIN instead of values, thanks Rob Collie for setting this trend )

Or even add Asset on Rows instead of class and get the right results:

Hope this gave you a little more insight into DAX. You can download the Excel 2013 workbook here: https://skydrive.live.com/redir?resid=7F4E0559CC74581A!1088