Implementing Conditional Formatting in Tabular
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:
- In Visual Studio 2010, 2012, or 2013, go to Tools->Extensions and Updates.
- 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.
