DAX Editor Adds Support for Tabular Default Members

UPDATE 4/24/2019   The new JSON-based Tabular schema doesn’t support extensions so Tabular Editor and BI Developer Extensions (BIDS) won’t work.

In my previous blog, I announced a few new features for DAX Editor for Tabular. Today, I checked in another change that adds support for default members. Currently, Tabular doesn’t have UI for defining default members. However, you can define default members in the MdxScript section of the BIM file using MDX syntax just like you can do so in Multidimensional.

The only issue is that if you make a change to any Tabular calculated measure in the designer, SSDT will regenerate the script and your manual changes will be lost. That is unless you use DAX Editor as its support for default members keeps them in the script. As a disclaimer, I took the backdoor approach for defining the default member syntax. The right approach would be to build upon the wonderful work of Nickolai Medveditskov and check the default member syntax so DAX Editor catches syntax errors as you type and when you attempt to save the measures. But that would have required a lot of work with Managed Babel. Moreover, the next version of SQL Server might support default members natively so all this work could be wasted.

So, I’ve decided to implement default members by allowing you to define a custom MDX script section in the *.dax file that is carried verbatim to the Tabular model without any syntax checking or changes. Of course, if you make an error, e.g. reference a column that doesn’t exist or use wrong syntax, you’ll find this pretty quickly when you attempt to open the *.bim file. To fix this, check your syntax in DAX Editor and save the changes to the *.bim file again. Or, open the *.bim file source and remove the custom MDX script which you’ll find inside the <MdxScript> element.

The syntax for default members is simple:

1. In the DAX file, add the following section at the top of the file. It actually can appear anywhere but I’d typically put it on top when working with cube scripts.

   — MDX SCRIPT —

   ALTER Cube CurrentCube Update Dimension [Product].[Is Finished Goods], Default_Member = [Product].[Is Finished Goods].&[TRUE];

   ALTER Cube CurrentCube Update Dimension [Currency].[Currency Code], Default_Member = [Currency].[Currency Code].&[USD];

   — MDX SCRIPT —

IMPORTANT Notice that the script block has to be enclosed with “— MDX SCRIPT –” comments exactly as they appear in the example. DAX Editor looks for this pair of comments to identify the custom script block.

1. Inside the comments, enter ALTER CUBE statements to set the default members using MDX syntax as you would do it in Multidimensional cube scripts. Notice the MDX UniqueMember syntax for the Default_Member property. In this case, I set two default members. Specifically, the Is Finished Goods column in the Product table is set to the TRUE member () and the Currency Code column in the Currency table is set to USD.
2. Click the “Save Measures to BIM File” button in the DAX Editor Toolbar to apply the DAX Editor changes to the *.bim file as usual.

I haven’t tested it yet but you should be able to add other MDX script commands to the DAX Editor script, such as KPI definitions, which DAX Editor doesn’t currently support.

Enjoy!