![]() Usually you check the selection of one value only. To get the selected value, use the VALUES function in the measure that uses the parameter. ![]() Figure 5 The Parameter table does not have relationships with other tables. In that case, all of the columns for each parameter have to participate in the cross join. You obtain such a table by performing a Cartesian product between all the possible values of each parameter (also called a cross join of parameters values). If many parameters will be used in your data model, you can add all of them to the same table in order to reduce the number of tables in the model. You might use up to three columns in case you want to add a textual description to the parameter value and to specify a particular display order by using the Sort by Column property. That table has no relationships with other tables, and usually has only one column, containing the parameter values. Complete PatternĪdd a Parameter table to the data model. This scenario is demonstrated later in the Limit Top N Elements in a Ranking example. Even if you cannot define a dynamic set in DAX, you can define a measure that returns BLANK for items that you do not want to show according to the filter selected by the user. You can parameterize the number of elements shown in a PivotTable according to a particular ranking. ![]() You will see an example later by looking at the Period Table pattern. This means using a SWITCH statement that, according to the value selected, returns a different expression for the same measure. You might decide to apply a different algorithm to the same measure. This can be useful to simulate different scenarios based on different values of parameters in a simulation (e.g., discount based on volume sales level, percentage growth of revenues, drop rate in customer retention, and so on). For example, when you have a model with measures that can receive a parameter for the calculation, this pattern enables the user to change the parameters of the algorithm. You can pass one or more parameters to an algorithm by using a table for each parameter. Use Cases Algorithm Parameter and Simulation Figure 4 The Scale parameter changes the presentation of the Sales Amount measure in every column. For example, in Figure 4, the Scale parameter displays different values in different columns. It is interesting to note that the parameter works not only with a slicer, but on rows and columns as well. This example uses a scale, but you can use the same technique in many other scenarios. This pattern is typically used when you want to inject a parameter inside a DAX measure. You define the Sales Amount measure as follows: Figure 3 Setting the Scale slicer selection to “1000” divides the original calculation by 1000 and shows the SalesAmount measure in thousands. With “1” selected, there are no changes to original measures. Figure 2 The Scale slicer selection can change the visualization of the SalesAmount measure in the PivotTable. Figure 1 The Scale table does not have relationships with other tables.Įvery measure in the data model has to divide the value by the scale selected, obtaining the value from a filter context containing a single selection, as shown in Figures 2 and 3. To do that, you must first create a Scale table that does not have any relationship with other tables in the data model, as shown in Figure 1. Suppose you want to provide the user with a slicer that can select the scale of measures to display, allowing the user to select a value in millions, thousands, or units. For example, the Parameter Table can choose the number of customers to show in a PivotTable sorted by sales amount, similar to a dynamic “top N customers” calculation, where a slicer selects the N th value. In most cases, users can select only one value, and multiple selections cannot perform a meaningful calculation. The DAX expression in measures checks the value selected and calculates the result accordingly. Its only scope is to offer a list of possible choices to the end user in order to alter the calculations performed by one or more existing measures. ![]() To use it, you must define a table that has no relationships with any other table. The Parameter Table pattern is useful when you want to add a slicer to a PivotTable and make it modify the result of some calculation, injecting parameters into DAX expressions.
0 Comments
Leave a Reply. |