zondag 14 mei 2017

Getting filtered data from a tabular with Excel


Building a PowerPivot model on filtered data of a table from a SQL Database is fairly simple. In the table properties you can change the table view to query view and change the query. I haven't found a similar way when using a tabular with Excel (realtime connection).

The only option I have found so far is using the CommandText in the connection properties, but that changes my look and feel of browsing the cube. The retrieved data from the tabular is just like one plain table. This blogpost will describe the way of adjusting the .odc file in Excel and getting the data as a plain table.

The goal of this blogpost is to show how to filter the factdata based on a dimension value (DimCustomer).

The Datamodel

I've created a simple model in SQL Server and imported it into the Tabular project. It is modeled as follows.

One Facttable with a key to the DimCustomer table and a measure Amount. I added a few records.

Some simple EVALUATES

In SSMS, I experimented with some EVALUATES to get some data from the Tabular. Below the result of this exercise.

--Getting the data from a table

--A simple  query (and join between tables)
,"TotalAmount", SUM(Fact[Amount])

--A simple query and a calculated measure is used
,"TotalAmount", Fact[TotalAmount]

--A calculated measure and a Filter
 ,"TotalAmount", Fact[TotalAmount]
 DimCustomer[Name] = "Hennie"

Adjusting the .odc file

From the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.

This is the result in the Connection properties in the Excel file. The CommandType combobox is changed to Default and in the CommandText box a DAX EVALUATE expression is presented.

The result in Excel

And below you can see the fields in the FieldList and as you can see the Look and Feel has changed of the FieldList. 


This blogpost is about how to filter the data in Excel from a Tabular Model.

Best regards,


woensdag 3 mei 2017

DAX : Selecting a measure with a Slicer


Someone asked me a question about selecting a measure in Excel with PowerPivot/Tabular and I founded a blog about selecting a measure written by James Thomas. I have made my own example and wrote this blogpost, about it. In this blogpost I'll describe the following steps:
  • Create some sample data.
  • Create a helper table.
  • Create the datamodel.
  • Create the measures.
  • Create a pivottable and add the properfields to the pivottable.
  • Testing the result.

The source data

I've created three tables: DimCustomer, FactSales and a Measure table.

I added these to the PowerPivot model and the datamodel looks like the following screenshot:

The DAX Expressions

I've created the following DAX Expression (the + 2, etc is for getting some different testdata)

MeasureA:=SUM([Amount]) +2      
MeasureB:=SUM([Amount]) + 3
MeasureC:=SUM([Amount]) +8
MeasureD:=SUM([Amount]) +4
MeasureValue:=switch (TRUE;
Min ('Measure'[MeasureID] ) = 1;
Min ('Measure'[MeasureID] ) = 2;
Min ('Measure'[MeasureID] ) = 3;
Min ('Measure'[MeasureID] ) = 4;

Configure the Pivottable

This is how the pivottable is setup. The MeasureName is added to the rows and the MeasureValue measure is in the VALUES area.

The result

Here is the result of the solution. Here you can see that the slicerbutton MeasureA and MeasureC selects only the measure A and C and that is exactly what I want.

Yet, another example of selection with the slicerbutton:

Here you can see the different measures according to the selected slicer buttons.


This blogpost is about selecting the proper measures with a slicer.