IntroductionBuilding 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).
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 EVALUATESIn 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 EVALUATE Fact --A simple query (and join between tables) EVALUATE SUMMARIZE (Fact ,DimCustomer[Name] ,"TotalAmount", SUM(Fact[Amount]) ) --A simple query and a calculated measure is used EVALUATE SUMMARIZE (Fact ,DimCustomer[Name] ,"TotalAmount", Fact[TotalAmount] ) --A calculated measure and a Filter EVALUATE FILTER( SUMMARIZE (Fact ,DimCustomer[Name] ,"TotalAmount", Fact[TotalAmount] ), DimCustomer[Name] = "Hennie" )
Adjusting the .odc fileFrom the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.
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.
ConclusionThis blogpost is about how to filter the data in Excel from a Tabular Model.