Parameters in DAX Measures

An expression in DAX can invoke the execution of another expression by referencing another measure. For example, you can write:

A := 24
B := 18
C := [A] + [B]
D := [C] * 3

Invoking a measure executes its expression. For this reason, if you want to change the value inside an expression depending on an external parameter, you have to define the logic in the inner expression. Consider a discount that is different depending on a certain condition, for example the fidelity card type of the customer. You might want to evaluate this:

[Amount] := SUMX ( Order, Order[Quantity] * Order[Unit Price] )

[Discounted Amount Gold] := [Amount] * (1 – 0.2) + 0.00

[Discounted Amount Silver] := [Amount] * (1 – 0.15) + 2.50

[Discounted Amount Bronze] := [Amount] * (1 – 0.10) + 5.00

[Regular Amount] := [Amount] * (1 – 0.00) + 10.00

[Final Amount] :=
SUMX (
    VALUES ( Customer[Level] ),
    SWITCH (
        Customer[Level],
        "Gold", [Discounted Amount Gold],
        "Silver", [Discounted Amount Silver],
        "Bronze", [Discounted Amount Bronze],
        [Regular Amount]
    )
)

If you want to create a more generic definition of the Final Amount measure, you might refactor your DAX code in the following way:

[Amount] := SUMX ( Order, Order[Quantity] * Order[Unit Price] )

[Discount Percentage] :=
SWITCH (
    VALUES ( Customer[Level] ),
    "Gold", 0.20,
    "Silver", 0.15,
    "Bronze", 0.10,
    0
)

[Shipping Amount] :=
SWITCH (
    VALUES ( Customer[Level] ),
    "Gold", 0.00,
    "Silver", 2.50,
    "Bronze", 5.00,
    10.00
)

[Final Amount] :=
SUMX (
    VALUES ( Customer[Level] ),
    [Amount]
        * ( 1 - [Discount Percentage] )
        + [Shipping Amount]
)

In this simple case, this approach is manageable. In more complex expressions, having to push the condition generating the parameter into an expression invoked internally to another makes your code very specific to a single data model, and hard to reuse in other models (sometimes into the same model when you have many measures). In certain cases, you want to pass two arguments to the Final Amount measure, without referencing external measure from within the Final Amount measure itself. In other words, imagine being able to call the Final Amount measure as it was a function, using this hypothetical syntax:

[Final Amount] := 
SUMX (
    VALUES ( Customer[Level] ),
    SWITCH (
        Customer[Level],
        "Gold", [Discounted Amount] ( 0.20, 0.00 ),
        "Silver", [Discounted Amount] ( 0.15, 2.50 ),
        "Bronze", [Discounted Amount] ( 0.10, 5.00 ),
        [Discounted Amount] ( 0.00, 10.00 )
    )
)

Unfortunately, DAX does not support arguments passed to a measure. However, we can use a technique similar to the Parameter Table pattern to obtain a similar behavior, resulting in the following syntax:

[Final Amount] :=
SUMX (
    VALUES ( Customer[Level] ),
    SWITCH (
        Customer[Level],
        "Gold", [Discounted Amount] ( Par1[Value] = 0.20, Par2[Value] = 0.00 ),
        "Silver", [Discounted Amount] ( Par1[Value] = 0.15, Par2[Value] = 2.50 ),
        "Bronze", [Discounted Amount] ( Par1[Value] = 0.10, Par2[Value] = 5.00 ),
        [Discounted Amount] ( Par1[Value] = 0.00, Par2[Value] = 10.00 )
    )
)

In reality, we are writing the following code (the syntax above is automatically translated in the equivalent CALCULATE version by DAX Formatter):

[Final Amount] :=
SUMX (
    VALUES ( Customer[Level] ),
    SWITCH (
        Customer[Level],
        "Gold", CALCULATE (
            [Discounted Amount],
            Par1[Value] = 0.20,
            Par2[Value] = 0.00
        ),
        "Silver", CALCULATE (
            [Discounted Amount],
            Par1[Value] = 0.15,
            Par2[Value] = 2.50
        ),
        "Bronze", CALCULATE (
            [Discounted Amount],
            Par1[Value] = 0.10,
            Par2[Value] = 5.00
        ),
        CALCULATE (
            [Discounted Amount],
            Par1[Value] = 0.00,
            Par2[Value] = 10.00
        )
    )
)

In order to have a parameter in a measure, you need a table with a column that contains all the possible values you might want to use. In this example, we created two hidden tables (Par1 and Par2), each one containing 10,000 values, with all the numbers from 0.00 to 100.00 with a precision of two decimal points. The size of such tables is relatively small, and it allows transferring a value through the filter context. By using this technique, you can define the Discounted Amount measure in this way:

[Discounted Amount] :=
[Amount] * ( 1 - VALUES ( Par1[Value] ) ) + VALUES ( Par2[Value] )

Measures using this technique should be hidden from the client. The user should never invoke them directly (a multiple selection in Par1 or Par2 table would break the calculation with an error).