When building dashboards it is important to understand the order of operations when an expression gets executed. This will help you performance tune and help you identify which options are possible, versus needing an alternate solution.

Execution Order

  1. Dollar Sign Expansion
  2. Set Analysis
  3. Expression

Scenario

Data:

Region Year Sales
North 2019 100
North 2018 75
North 2017 50
South 2019 175
East 2019 85
East 2018 145
South 2017 30
South 2018 90

Expression Example:=Sum({<Region={'North','East'}, Year={$(=Year(Today()))}>} Sales)

Dollar Sign Expansion

Dollar Sign Expansion $() is a way to layer execution steps within your code. The most common way is replacing a variable with it’s value, however it can be extended much futher.

Before the expression is executed, all of the dollar sign expansions are calculated first. You can potentially have up to a 1000 nested dollar sign expansions, if you did they would all have to calculated before the interpreter is ready to start on the final expression.

Example: $(=Year(Today())) = 2019

Returning: =Sum({<Region={'North','East'}, Year={2019}>} Sales)

Set Analysis

One very important thing to understand from a functionality perspective, is that set analysis defines the data set for the expression before it gets evaluated. This is why you cannot utilize dimensionality to it.

To put it in SQL terms, set analysis would be defining a temp table for which your expressions on. The dimension values available are what will be queried over. This work flow doesn’t allow you to define a different set for each dimension value.

Example: {<Region={'North','East'}, Year={2019}>}

Returns the following data:

Region Year Sales
North 2019 100
East 2019 85

Dimensions

We touched on this a little bit above. The resulting dimension values defined in the set will then define what the expression is calculated over.

*Assuming the dimension is in the chart

Of the available data the possible dimension values are:

Region Year
North 2019
East 2019

Expression

Lastly the expression is executed over the dimension values in the hypercube.

Example: Sum(Sales)

Total Return: 185

FAQ

Here are some of the questions we can answer by knowing this:

  • Why are calculated dimensions slow?
    • They are done at a record level and need to be evaluated before the expression is.
  • Why is set analysis so fast?
    • To put it simply, it’s because we are limiting the amount of data we are crunching.
  • Why does everyone suggest set analysis over if statements?
    • Set analysis limits the data before the expression. If statements are executed in the expression over the data record by record.
  • Why can’t we show max year sales per region using set analysis?
    • Set analysis defines the available dimension values, therefore cannot leverage them. This use case would require record by record execution.

Tips

  • Always try and move calculated dimension criteria to set analysis.
  • Stay away from If statements.
    • If you require dimensionality in your expression, use Pick(Match()) instead.
  • You can leverage dollar sign expansion to create a dynamic UI/UX.