Organizing and keeping track of your DAX measures in a tabular model can be a challenging task, especially when working with larger Analysis Services projects. Visual Studio does not give you much help in organizing them and it is easy to end up with a messy model where code is duplicated among measures.
Excel and PowerShell to the rescue!
An alternative approach from keeping your DAX measures within your Analysis Services project would be to store them in well-structured Excel document(s) instead. Such an Excel document could be structured in the following way:
The following metadata (columns) are used to describe the DAX measure:
- MeasureName – the name of the measure.
- DAXFormula – the full DAX expression that is used to calculate the measure.
- Format – the formatting formula that should be applied to the measure (if any).
- VirtualTable – the name of the table (a virtual table that should not exist in the model) that the measure should be placed in. Note that all measures are placed in virtual tables, not physical ones already existing in the model.
- Folder – the name of the display folder (if any) within the virtual table.
- Hidden – a flag indicating whether the measure should be hidden or not.
- DAXTemplate – a place to create reusable DAX expressions templates that can be applied to the measure. More on this later.
- Description – a user friendly description of the measure (used as the description property in the model).
By using this approach, instead of placing the measures directly in the model, you will get a much better overview and organization of your DAX measures. The only drawback is that you will not get DAX intellisense into Excel (you will probably use Power BI to write the initial DAX and copy/paste it into the Excel document).
So, now that you have organized your measures – how to you deploy them to your model?
This is where PowerShell comes into play.
The basic approach for the PowerShell script to deploy the contents of the Excel file(s) are:
- Read the measure metadata from all XLSX files in the directory.
- Create a TMSL script from the contents in (1).
- Deploy the TMSL script to the Analysis Services instance (using Delete/Create approach).
What about the DAXTemplate property?
Since we are using PowerShell we can apply any type of logic to the DAX expressions. The DAXTemplate property is such an example. The DAXTemplate property can be used to apply a reusable piece of DAX code on your measures. Each DAXTemplate correspond to a PowerShell function that contains code to manipulate the DAX expression. Here is an example (just for demonstration purpose):
The above DAXTemplate is called TimeRange and is used to make it possible to toggle between YTD and LastMonth for any measure in the model, simply by adding TimeRange as DAXTemplate in the Excel sheet. This approach significantly reduces the amount of duplicated code.
The solution to manage DAX measures using this approach consists of the following ps1-files:
- Excel2TMSL.ps1 –main file that does the work. This is the file to be executed.
- Excel2TMSL.include.ps1 –file that holds the DAXTemplate functions.
- Excel2TMSL.Functions.include.ps1 – file that holds helper functions, called from Excel2TMSL.ps1.
The full source code can be found here (with sample XLSX files):