Archive for year: 2015

As I mentioned here and here, Marco and I teamed up to invigorate the DAX Editor with some new features. Today we officially released the latest updates and published in to the Visual Studio Gallery. The easiest way to install is to do it directly from Visual Studio/SSDT:

1. In Visual Studio 2010, 2012, or 2013, go to Tools->Extensions and Updates.
2. Click the Online tab and search for “dax” or “dax editor”. Note that if you use Visual Studio 2013, you need to make a configuration change in devenv.exe.config that I explained here.

In the second post, I’ve mentioned that I’ve added the option to inject some custom MDX script in the Tabular script, such as to set up default members. You can also use the custom script to implement conditional formatting – a feature that Tabular doesn’t support natively. Suppose that you want to change the front color of the measure Products with Negative Stock to Red when it’s above zero.

The following scope assignment in DAX Editor gets the job done:

— MDX SCRIPT —

       SCOPE( Measures.AllMembers );

              IF Measures.CurrentMember IS [Measures].[Products with Negative Stock] AND [Measures].[Products with Negative Stock] > 0 Then

                     FORE_COLOR(THIS) = 255

              End If; 

       END SCOPE;

— MDX SCRIPT ––

The more convoluted syntax in this case is because [Products with Negative Stock] is a calculated member. If it was a regular measure, the scope assignment could be simplified:

— MDX SCRIPT —

       SCOPE({[Measures].[Products with Negative Stock]});

              FORE_COLOR(THIS) = Iif (Measures.CurrentMember > 0, 255, 0);

       END SCOPE;

— MDX SCRIPT ––

Yet, another way to do it is to define the measure using MDX thanks to the fact that everything defined between the “–MDX SCRIPT –” placeholders is carried verbatim to the cube script. We can say that that’s another DAX Editor feature especially if you need MDX features that are not supported in DAX, such as hierarchy navigation (ANCESTOR, DESCENDANTS, etc.) or KPIs.

— MDX SCRIPT —

    CREATE MEMBER CURRENTCUBE.[Measures].[Products with Negative Stock 1] AS <some calculation>, FORE_COLOR = IIF([Measures].[Products with Negative Stock 1] > 0, 255 /*Red*/, 0 /*Black*/);

— MDX SCRIPT –-

You got the idea. You can stuff in any valid MDX statement that Multidimensional supports in the cube script.

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.

As it stands, Tabular (versions 2012 and 2014) doesn’t have the equivalent of a cube script. Instead, the developer has to use the Measures grid to maintain DAX calculated measures. This is OK with a few measures but as the number of measures grows, the development experience suffers because it’s getting harder to locate these measures. Besides, every time you make a change, you need to wait for the Tabular environment to refresh which gets annoying quickly. However, you can use the DAX Editor community sample to simulate a Tabular script. DAX Editor allows you to extract all measures from a Tabular model and maintain them outside the model in a DAX file. DAX Editor was initially developed by Microsoft (kudos to Nickolai Medveditskov).

Marco Russo and I teamed up to add some new features to DAX Editor as follows:

1. Support of Visible and Description properties. Although only Power View supports field descriptions (Excel doesn’t), entering user-friendly descriptions allows you to implement self-documented models. For example, you can get a list of all measures and descriptions from the $SYSTEM.MDSCHEMA_MEASURES DMVs. The following example shows you how to use Visible and Description properties:

    CREATE MEASURE ‘Internet Sales'[Internet Distinct Count Sales Order]=DISTINCTCOUNT([Sales Order Number]) CALCULATION PROPERTY NumberWhole Visible=False ThousandSeparator=True Format=’#,0′ Description=’Returns unique number of sales orders’;

 Notice that even if don’t want to overwrite the measure format, you still have to specify the default General format so that DAX Editor can parse the syntax:

CREATE MEASURE ‘Internet Sales'[Internet Distinct Count Sales Order]=DISTINCTCOUNT([Sales Order Number]) CALCULATION PROPERTY General Visible=False Description=’Returns unique number of sales orders’;  

     2.   Support of Visual Studio 2013 although it requires a workaround to fix the bindings for the Microsoft.VisualStudio.Package.LanguageService.* dlls. Specifically, you need to modify the VS 2013 devenv.config (C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe.config) as follows:

<dependentAssembly>

       <assemblyIdentity name=“Microsoft.VisualStudio.Package.LanguageService.11.0“ publicKeyToken=“b03f5f7f11d50a3a“ culture=“neutral“/>

       <bindingRedirect oldVersion=“2.0.0.0-11.0.0.0“ newVersion=“11.0.0.0“/>

</dependentAssembly>

<dependentAssembly>

       <assemblyIdentity name=“Microsoft.VisualStudio.Package.LanguageService.10.0“ publicKeyToken=“b03f5f7f11d50a3a“ culture=“neutral“/>

       <bindingRedirect oldVersion=“2.0.0.0-11.0.0.0“ newVersion=“10.0.0.0“/>

</dependentAssembly>

We still have some outstanding work to test and make the binaries available for download and on Visual Studio Extension Gallery. Meanwhile, you can get the latest source (build 42258 as of now) and build it as follows:

1. Install Visual Studio 2010 (C#) and the Visual Studio 2010 SDK. You must use Visual Studio 2010 to open the DAX Editor source as Visual Studio 2012 and above will upgrade the project.
2. Open the DAX Editor source in Visual Studio 2010 and build the project.
3. If you use Visual Studio 2013, fix the bindings as I explained above.
4. Double-click the DaxEditor.vsix in the bin/debug folder and associated with the desired versions of Visual Studio you have on your machine.

The next thing on my list is to add support for Tabular default members and KPIs. The usual disclaimer applies that the sample is not supported and its features might not work with future editions of SQL Server. Other than this, enjoy it and I hope you find it useful as I do with my current projects.