SSAS Tabular Measures: Do staged facts from DW need to be expressed in DAX as measures to use as measures?

Question

I have a SQL DW that has staged facts -- already aggregated -- to the granularity of multiple dimensions.

For example these dimensions:

• Period
• Product
• BranchLocation

And this fact:

• Revenue (aggregated sum per period x product x branch)

When I bring this into a SSAS Tabular model, deploy it, and try to use it (say in Excel, for example), I am confronted with the realization that my facts must be expressed as measures. I say this because I cannot use the fact as a value in a pivot table in Excel.

Is there a simple way to convert or cast each already aggregated fact into measures?

Answer 1

Is there a simple way to convert or cast each already aggregated fact into measures?

Sure. Just create simple measure for each fact. You can take it a step further and hide the fact column from client tools, so that only the Measure appears. You need to specify how the model will further aggregate this fact in the measure expression. EG

Sales = sum(FactResellerSales[SalesAmount]) 

Note that Measures are only required by some clients (like Excel), that use the MDX interface for the model. Power BI reports support using pretty much any column anywhere on a report.