How to Write Report-Scoped DAX Expressions Using AtScale Dimensional Models

In my last post, I demonstrated the many advantages of using AtScale Models to power your Business Intelligence workloads, including human-friendly SML syntax, Git integration, multi-tool support, zero-extract Live Connections, and proven BI Modeling capabilities. In this post, I’ll demonstrate how Power BI Analysts can enjoy these benefits while still customizing their reports with DAX expressions.

Computing Ratios by Controlling the Filter Context

A measure’s value doesn’t have much meaning unless compared to something.  As a result, meaningful business metrics are often presented as ratios or percentages.  Every reporting tool on the planet makes it easy to understand a measure’s contribution to the whole when presented in the form of a pie chart.  Unfortunately, when creating dashboards or performing ad-hoc calculations, we can’t always use pie charts to compute simple percentages.  If you are familiar with DAX, then you are undoubtedly familiar with the concept of removing dimensions from the Filter Context to create ratios.  

When PowerBI connects to multi-dimensional data sources, like SSAS MD, PowerBI disables the DAX Expression “Calculator” icon. So, how can you write such an expression if you are connected to an AtScale Multi-Dimensional Model?  As it turns out, AtScale supports both Tabular and Multi-dimensional connections to the same model!  

To make a Tabular-connection to the AtScale model, simply append the “?daxdialect=tabular” query string parameter to your connection URL.  The engine can be configured to treat all DAX sessions as tabular if preferred.  The important takeaway is that no other product on the market supports both Tabular and Multi-dimension sessions to the same model.  This is why AtScale users enjoy unrivaled parity between PowerBI and Excel Live Connections to the same model instance.  See my previous post about modernizing data models to learn more about the problem with hierarchies when making live connections to a tabular model using Excel.

Now that we’ve made a Tabular connection to our “AS Adventure Small” Model, let’s write a DAX expression that will compute the Product Subcategory’s contribution to the larger context’s sales. The expression will be the ratio of the model-side [Internet Sales Amount Local] measure evaluated within the current query context divided by the [Internet Sales Amount Local] measure with the Product Subcategory removed from the Filter Context.  Composing the expression this way will give it a high degree of reusability, so it will work with whatever dimensions the user includes in the visualization without additional tweaking.  For Tableau users, this is equivalent to Talbeau’s LOD EXCLUDE functionality.  

Let’s start by creating a Matrix visualization to see our model-side [Internet Sales Amount Local] measure broken down by Year/Month and Product Subcategory.

Figure 1. A Matrix Visualization of [Internet Sales Amount Local] grouped by the Retail445 Month Level and Product subcategory.

The syntax for referring to an AtScale Model Attribute is: ‘DimensionName'[AttributeName].  In this case, the ALL(‘Product'[Subcategory]) function call instructs Power BI to write a DAX script variable that removes the Subcategory attribute from the filter context when evaluating CALCULATE.  As you may have noticed, the syntax for referencing an AtScale Measure is to refer to it enclosed in brackets, [Internet Sales Amount Local].  The CALCULATE function returns the result as a scalar value for each row in the resulting table.  Figure 2. Shows the addition of ‘salesExcludeCategoryDetails’ to the visualization. 

Figure 2. ‘salesExcludeCategoryDetails’ shows how the ALL function removes the AtScale Attribute ‘Product'[Subcategory] from the Filter Context used to compute the [Internet Sales Amount Local] model-side Measure.

Notice that we reference the [salesExcludeCategoryDetails] expression using the same ‘Dimension'[Attribute] syntax, only this time the dimension for Measures called ‘CubeMeasures’.  Figure 3. shows the addition of salesPctOfDisplayParent to the visualization.  I turned on Conditional Formatting to make the larger contributors stand out.

Figure 3. ‘salesPctOfDisplayParent’ shows each Subcategory’s sales relative to the larger context subtotal, in this case ‘Retail445′[Reporting Month].

Figure 4. ‘salesPctOfDisplayParent’ performs as desired without modification when adding additional dimensions, such as ‘Product'[Color].

Conclusion 

Not only does AtScale support both Multidimensional and Tabular Live Connections to provide unrivaled consistency across tools, but Power BI users can use DAX expressions to further customize their reports. AtScale maintains the high-value aspects of multi-dimensional models and modernizes the stack by introducing:

• Power BI DAX Report-Scoped Expressions.
• Human-friendly SML to leverage component reusability.
• Git integration to support change-control best practices.  
• Consistent Reporting-Tool Experience across Power BI, Excel, Tableau, Looker, etc.
• A large number of supported back-end data warehouses.

In my next post I’ll cover AtScale’s more advanced capabilities to create customized calculations on the fly without changing the server-side model.