Understanding Evaluation Contexts in DAX

Creating a parameter table

In this chapter, you learned many theoretical concepts about evaluation contexts. It is now time to use some of them to solve an interesting scenario and learn a very useful technique, that is, the use of parameter tables.

The idea of a parameter table is to create a table that is unrelated to the rest of the data model, but you will use it internally in DAX expressions to modify their behavior. An example might help to clarify this. Imagine you have created a report that shows the sum of sales amount and, because your company sells many goods, the numbers shown in the report are very large. Because our sample database does not suffer from this problem, instead of using the SalesAmount column, we have created a measure that sums the SalesAmount cubed, so that numbers are bigger and the described scenario is more realistic. In Figure 4-25 you can see this report.

FIGURE 4-25 Reading reports with big numbers is sometimes difficult.

The issue with this report is that the numbers are large and they tend to be hard to read. Are they millions, billions, trillions? Moreover, they use a lot of space in the report, without carrying much information. A common request, for this kind of report, is to show the numbers using a different scale. For example, you might want to show the values divided by a thousand or a million, so that they result in smaller numbers, still carrying the useful information.

You can easily solve this scenario by modifying the measure and dividing it by a thousand. The only problem is that, depending on the relative scale of numbers, you might want to see them as real values (if they are small enough), divided by thousands or divided by millions. Creating three measures seems cumbersome at this point and we want to find a better solution that removes the need of creating many different measures.

The idea is to let the user decide which scale to apply in the report when using a slicer. In Figure 4-26 you can see an example of the report we want to build.

FIGURE 4-26 The slicer does not filter values here. It is used to change the way numbers are shown.

The interesting idea of the report is that you do not use the ShowValueAs slicer to filter data. Instead, you will use it to change the scale used by the numbers. When the user selects Real Value, the actual numbers will be shown. If Thousands is selected, then the actual numbers are divided by one thousand and are shown in the same measure without having to change the layout of the pivot table. The same applies to Millions and Billions.

To create this report, the first thing that you need is a table containing the values you want to show on the slicer. In our example, made with Excel, we use an Excel table to store the scales. In a more professional solution, it would be better to store the table in an SQL database. In Figure 4-27 you can see the content of such a table.

FIGURE 4-27 This Excel table will be the source for the slicer in the report.

Obviously, you cannot create any relationship with this table, because Sales does not contain any column that you can use to relate to this table. Nevertheless, once the table is in the data model, you can use the ShowValueAs column as the source for a slicer. Yes, you end up with a slicer that does nothing, but some DAX code will perform the magic of reading user selections and further modifying the content of the repor.

The DAX expression that you need to use for the measure is the following:

[ScaledSalesAmount] :=
IF (
    HASONEVALUE ( Scale[DivideBy] ),
    DIVIDE ( [Sales Amount], VALUES ( Scale[DivideBy] ) ),
    [Sales Amount]
)

There are two interesting things to note in this formula:

• The condition tested by the IF function is: HASONEVALUE ( Scale[ShowValueAs] ). This pattern is very common: you check whether the column of the Scale table has only one value visible. If the user did not select anything in the slicer, then all of the values of the column are visible in the current filter context; that is, HASONEVALUE will return FALSE (because the column has many different values). If, on the other hand, the user selected a single value, then only that one is visible and HASONEVALUE will return TRUE. Thus, the condition reads as: “if the user has selected a single value for ShowValueAs attribute.”
• If a single value is selected, then you know that a single row is visible. Thus, you can compute VALUES ( Scale[DivideBy] ) and you are sure that the resulting table contains only one column and one row (the one visible in the filter context). DAX will convert the one-row-one-column table returned by VALUES in a scalar value. If you try to use VALUES to read a single value when the result is a table with multiple rows, you will get an error. However, in this specific scenario, you are sure that the value returned will be only one, because of the previous condition tested by the IF function.

Therefore, you can read the expression as: “If the user has selected a single value in the slicer, then show the sales amount divided by the corresponding denominator, otherwise show the original sales amount.” The result is a report that changes the values shown interactively, using the slicer as if it was a button. Clearly, because the report uses only standard DAX formulas, it will work when deployed to SharePoint or Power BI, too.

Parameter tables are very useful in building reports. We have shown a very simple (yet very common) example, but the only limit is your imagination. You can create parameter tables to modify the way a number is computed, to change parameters for a specific algorithm, or to perform other complex operations that change the value returned by your DAX code.