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
- Dollar Sign Expansion
- Set Analysis
- 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.
