Using DAX Editor

DAX Editor is a free Visual Studio extension that extracts in a text file all the DAX measures from a SSAS Tabular model stored in a BIM file. The text file obtained in this way can be modified using any editor, including Visual Studio itself. The file has a .dax extension. You can add, modify, and delete measures by simply editing this file. All the changes are applied to the BIM file and then persisted to the workspace as soon as you open the BIM file again.

In order to avoid damaging or losing part of your model, it is important to know how DAX Editor works. For this reason, you should always create a backup of the model before starting to use DAX Editor, and we suggest you to read all the sections of this article, including the description of the internal behavior of DAX Editor. This will be helpful to manage any troubleshooting.

DAX Editor can be installed on Visual Studio 2012, 2013, and 2015. It supports compatibility levels 1103 and 1200 of Tabular models (both XML and JSON formats of BIM files). Today, DAX Editor is not available for Power BI or Power Pivot models: it would be interesting, but we do not have the resources for such a development. The project is open source on GitHub and any contribution to create a version for the other tools would be very welcome.

DISCLAIMER: you use DAX Editor at your own risk, it is not a Microsoft supported product and you should always create a backup copy of your BIM file before editing it with DAX Editor.

Install DAX Editor

DAX Editor is available in the Visual Studio Gallery. You can access from Visual Studio by selecting the Tools \ Extension and Updates command. In the dialog box you select Online and search for DAX Editor in the search box. Once you see the DAX Editor in the search results, you click the Download button, which downloads and installs DAX Editor in Visual Studio.

If you cannot connect to the Visual Studio Gallery from Visual Studio (for example, because of firewall policies applied to your development environment), you can download the corresponding VSIX file from the Visual Studio Marketplace and install the extension by simply opening the VSIX file on the PC where you have installed SQL Server Data Tools (SSDT).

Edit measures with DAX Editor

Open a Tabular model in SSDT, so that the Model menu is active in Visual Studio. At this point, you can select the Get measures from BIM file command, which creates a new DAX file containing all the measures in your BIM file. The two files will have the same name before the extension, so by default you have a Model.dax file corresponding to the Model.bim file.

The .dax file contains a textual representation of all the measures in the Tabular model. You can edit this file in Visual Studio editor in the same way you can edit any text file. Visual Studio recognizes the .dax extension and provides syntax highlighting for keywords, functions, and other elements of the DAX language.

You can add new measures, change or delete existing measures by simply modifying the .dax file. You will find more details about this syntax in a following section of this article.

When you edit the .dax file, you have a DAX menu in Visual Studio providing commands to format the code and to apply the changes to the model in the BIM file.

Once you complete the editing of your measures, you can apply the changes to the BIM file by invoking the Save measures to BIM file command. You should close the BIM file before doing so, and you should open the BIM file in Visual Studio immediately after you apply the changes. In other words, follow these steps as a best practice:

Open BIM file in SSDT
Edit BIM file in SSDT
Save BIM file
Invoke Get measures from BIM file
Edit DAX file in SSDT
Close BIM file in SSDT
Invoke Save measures to BIM file
Open BIM file in SSDT

When you choose Reformat selection or Reformat document from the DAX menu, by default your code is formatted using the DAX Formatter service. If you do not have an Internet connection or you prefer to use the internal formatting capability (which is more trivial and not fully tested), you can change the Formatter property in the Options dialog box (Tools \ Options…), specifying “DAX Editor” instead of www.daxformatter.com.

NOTE: the Enable DAX changes to online database setting activates a number of features inherited from the previous versions of DAX Editor, which works only in model compatibility 110x. However, these features are not supported in model compatibility level 1200, and they have not been tested in the new version of DAX Editor. For this reason, the default setting of this property is false.

How DAX Editor works

When you edit a Tabular model in SSDT, you modify a BIM file, which is a single text file containing the serialization of an object model describing the entities in your Tabular model. Such a serialization is made in XML when you have a model in compatibility level 110x, and it is made in JSON for compatibility level 1200. The differences are not limited to the serialization format: the object model itself is very different between the two compatibility levels, even if these differences are hidden by the development environment in Visual Studio.

DAX Editor extracts the measures included in a Tabular model stored in a BIM file, managing the differences between different compatibility levels and creating a text file that uses always the same syntax. Thus, the measures created in a model in compatibility level 1200 can be copied to a model in compatibility level 110x. For example, you might need this to backport a set of measures in an older version of a data model that you cannot upgrade for any reason (e.g. a customer did not upgrade to SQL Server 2016 yet, and you deploy the same model to several customers).

When you edit the DAX code in the DAX Editor window, an internal parser shows syntax errors in the code. However, not all of the syntax errors are recognized. For example, if you reference a table, column, or measure that does not exists in the data model, DAX Editor will not notify any error and you will see the error in the standard editor in Visual Studio.

When you invoke the Save Measures to BIM File command, the DAX file is saved to disk and read from an internal parser. The DAX expression and the properties of every measure are saved in the BIM file. New measures in the DAX file are added to the BIM file, whereas existing measures are updated. DAX Editor removes from the BIM file all the measures that are not defined in the DAX file. In other words, the DAX file has all the measures of the model, and it always overwrites any previous version. For this reason, it is important to keep a copy of the BIM file before using DAX Editor (using Visual Studio Team Services or GitHub for your Tabular project is a good idea to keep track of versions and changes over time).

It is better to close the BIM file before saving measures edited in DAX Editor. After you executed Save Measures to BIM File, if you had the BIM file open in Visual Studio, you should close it. If you had unsaved changes in the BIM file, saving them would overwrite any change applied by DAX Editor. Remember: DAX Editor works on the BIM file and does not intercept the operations made in Visual Studio on the same model.

However, modifying the BIM file does not update the workspace database used by SSDT. In order to do this, you just have to open the BIM file in Visual Studio after you executed the Save Measures to BIM File command. Doing so, SSDT will synchronize the workspace database with the current version of all the measures. You will pay the price of this operation only once, instead of waiting for a large number of validations happening every time you modify a single measure in the editor in Visual Studio.

Syntax of .dax files by example

The suggested way to get the exact syntax of a .dax file is to check the file created by applying the corresponding syntax in Visual Studio. The reason is that DAX Editor tries to keep the additional properties used by Visual Studio, which are not used by Analysis Services. This is important to keep a good compatibility with the Visual Studio editor, but this introduced some additional complexity in the syntax, with some small differences between difference versions of SSDT and Visual Studio.

The .dax file has a CREATE MEASURE statement for each measure, and all the formatting properties of the measure are included within the same statement, which ends with a semi-colon (;). For example, this is a measure definition that does not have any customization applied to formatting properties.

CREATE MEASURE 'Sales'[Discounts YTD] =
    TOTALYTD ( [Discounts], 'Date'[Date] ) ;

Every measure specifies an explicit table in its definition, which corresponds to the table that will show the measure in Visual Studio and in all the client tools. Additional properties are added after the CALCULATION PROPERTY keyword. If you want to apply a custom format, you can use this syntax:

CREATE MEASURE 'Sales'[Discounts YTD] =
    TOTALYTD ( [Discounts], 'Date'[Date] )
CALCULATION PROPERTY GENERAL
    FORMAT = '0.00' ;

CREATE MEASURE 'Sales'[Cost] =
    SUMX ( Sales, Sales[Unit Cost] * Sales[Quantity] )
CALCULATION PROPERTY GENERAL
    FORMAT = '#,0.0' ;

A percentage is just another type of custom format:

CREATE MEASURE 'Sales'[Margin%] = [Margin] / [Sales Amount]
CALCULATION PROPERTY GENERAL
    FORMAT = '0.00 %;-0.00 %;0.00 %' ;

Dates have particular format types, and even standard formats listed in Visual Studio are considered custom formats in the .dax file, with the only exception of the short date, which has a specific format type:

CREATE MEASURE 'Sales'[Last Day] =
    LASTDATE ( 'Date'[Date] )
CALCULATION PROPERTY DATETIMESHORTDATEPATTERN
    FORMAT = 'Short Date' ;

CREATE MEASURE 'Sales'[Current Day] =
    TODAY ()
CALCULATION PROPERTY DATETIMECUSTOM
    FORMAT = 'yyyy-MM-dd'
    ADDITIONALINFO = 'LCID="1033" Group="ShortDate" FormatString="yyyy-MM-dd"' ;

The ADDITIONALINFO keyword contains a string with a number of properties used only by Visual Studio user interface, whereas the information used by Analysis Services is only the string assigned to FORMAT. A standard currency format defined in Visual Studio corresponds to the following:

CREATE MEASURE 'Sales'[Sales Amount] =
    SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )
CALCULATION PROPERTY CURRENCY
    ACCURACY = 2
    FORMAT = '\$#,0.00;(\$#,0.00);\$#,0.00'
    ADDITIONALINFO = 'LCID="1033" DisplayName="$ English (United States)" Symbol="$" PositivePattern="0" NegativePattern="0"' ;

A measure that is hidden to the client tools has the property VISIBLE set to FALSE (the default is TRUE):

CREATE MEASURE 'Sales'[Margin] = [Sales Amount] - [Cost]
CALCULATION PROPERTY CURRENCY
    VISIBLE = FALSE
    ACCURACY = 2
    FORMAT = '\$#,0.00;(\$#,0.00);\$#,0.00'
    ADDITIONALINFO = 'LCID="1033" DisplayName="$ English (United States)" Symbol="$" PositivePattern="0" NegativePattern="0"' ;

The description of a measure can be included in its DESCRIPTION property (in case a single quote is included, it is transformed into a grave accent by DAX Editor, which transforms that character in a single quote when the measures are saved in the BIM file).

CREATE MEASURE 'Sales'[Discounts] =
    SUMX ( Sales, Sales[Unit Discount] * Sales[Quantity] )
CALCULATION PROPERTY GENERAL
    DESCRIPTION = 'Simple measure with generic format' ;

The display folder is included in the DISPLAYFOLDER property.

CREATE MEASURE 'Sales'[Sales Amount YOY %] =
    VAR Curr = [Sales Amount]
    VAR Prev =
        CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date1] ) )
    RETURN
        DIVIDE ( Curr - Prev, Prev )
CALCULATION PROPERTY CURRENCY
    ACCURACY = 2
    FORMAT = '\$#,0.00;(\$#,0.00);\$#,0.00'
    ADDITIONALINFO = 'LCID="1033" DisplayName="$ English (United States)" Symbol="$" PositivePattern="0" NegativePattern="0"'
    DISPLAYFOLDER = 'Compare' ;

CREATE MEASURE 'Sales'[Discounts YOY %] =
    VAR Curr = [Discounts]
    VAR Prev =
        CALCULATE ( [Discounts], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    RETURN
        DIVIDE ( Curr - Prev, Prev )
CALCULATION PROPERTY GENERAL
    FORMAT = '0.00 %;-0.00 %;0.00 %'
    DISPLAYFOLDER = 'Compare' ;

Limitations of DAX Editor

Current limitations of DAX Editor:

KPI are not supported: you cannot get the measures from BIM file if a KPI exists, you should remove all the KPIs before using DAX Editor.
BIM file has to be opened to propagate the changes to the workspace (follow the best practices described in the article).
Comments in measures are note supported. You can get the comments in the DAX file, but the comments are lost when you use Save Measures in BIM file.

Future releases of DAX Editor might remove these limitations (I will update the article in that case).