Marco Russo has created a great tool for SSAS tabular that lets you edit measure definitions (which you should read here first if you haven’t done yet).
In this article I’ll show you how you can use it to import multiple measures from different tabular models into your current model.
The way the DAX-editor works is that it exports the existing measures from your model as a text file and imports them back after you’ve done your transformations. My technique will add the measures from the other model automatically to the existing measures so that both can be loaded back into your current model. In addition to that, you will have a UI with a process that guides you through the necessary steps that come with a task like that, which are:
- select only those measures that you actually need
- check references to existing measures and columns from the import model and manage their handling
- allocate the tables into which these measures are going to be imported
This will all be done in a Power Query-powered Excel workbook that you can download here: DaxEditorEditor_Final2.xlsm
These are the steps:
1. Get measures from BIM-file of the import-model:
2. Transform document
3. paste into Excel in sheet “PasteCodeFromDifferentModel”
4. Repeat 1-3 for measures from target-model, insert into sheet “PasteCodeFromTargetModel”
5. Move to sheet “ModelTranslation”, check the blue table -> rightclick mouse -> “Refresh”
6. Check the measures you want in sheet “ModelTranslation”:
7. Allocate missing measures
8. Allocate missing columns
9. Click refresh Macro
10. Check & edit red fields in EditMeasures in sheet “EditMeasures”
11. In sheet “CodeExportBack” check A1 -> rightclick mouse -> Refresh: Then copy the rows and paste them back into the Model.dax and reformat document. Close the .bim-file of your target model before loading the new measures back:
And: You can also use this workbook without the measures from a different model. If you want to move multiple measures from one table to another for example or add or edit multiple properties at once. This might be more efficient here, as you don’t have to type in the name of the properties and the navigation trough the table might simply be quicker than to manually move to the respective positions in the text-editor. Basically everything that is not editing the measure-expression itself – editing the DAX-expression is best be done in the other editor, as you have all the nice formatting there.
Limitations:
- Currently only works for compatibility level 1200. Might provide solution for lower ones, if there is enough demand
- There might be some properties that I’ve missed
- There also might be issues with different locales
- … – just let me know.
Please let me know if you find this helpful and where this solution could be improved. Also stay tuned for the next episode where I’ll show you how to import measures from your DAX library in Excel into your SSAS Tabular model.
Enjoy & stay queryious 🙂