![]() ![]() Each column shows the Sales Amount of only the top N products, where N is determined by the column header. Imagine needing a report like the one in Figure 4, where each column filters a different number of products with the highest Sales Amount. The relationship between two or more parameters is implicitly found in the Discount table and directly affects the slicers through cross-filtering. Therefore, when the Min Quantity slicer selects 3, the Discount slicer only shows values less than or equal to 30%. The Discount table does not include combinations such as 3 for Min Quantity and 50% for Discount. SELECTCOLUMNS ( GENERATESERIES ( 1, 10, 1 ), "Min Quantity", ) ![]() SELECTCOLUMNS ( GENERATESERIES ( 0, 19, 1 ), "Discount", / 20 ) The Discounted Amount measure is identical to the measure used for the multiple dependent parameters example – it prepares a table in the Orders variable that includes the quantity and amount of each order, and then performs the proper calculation by iterating over the table in Orders: Figure 3 Because the selection on Min Quantity is three, the Discount slicer only shows options up to 30%. Figure 3 shows an example of this scenario. When the user makes a selection in the Min Quantity slicer, the Discount slicer only shows allowed percentage values according to the Min Quantity selected. In other words, if a user selects 3 for Min Quantity, the maximum Discount available is 30%. The additional requirement is that the discount percentage cannot be greater than 10 times the Min Quantity. Imagine the scenario of the “Multiple independent parameters” pattern with two parameters: Min Quantity and Discount. If a calculation depends on multiple parameters with limited available options, then a single table with one column for each parameter can store one row for each valid combination of the parameter values. In order to apply restrictions to the available combinations of parameters in different slicers, it is necessary to implement the multiple dependent parameters pattern. In other words, a user can choose any combination of the two parameters, and the selection made in one parameter slicer does not affect the values available in other parameter slicers. ( 1 - Disc ) * using multiple parameter tables, the parameters are independent from each other. The result is obtained by iterating over the table in Orders, applying the discount to each individual order if the total quantity exceeds the defined boundary: The implementation of the Discounted Amount measure first prepares a table in the Orders variable including the quantity and amount of each order. Figure 2 Discounted Amount applies a 15% discount to the orders with more than 6 products. ![]() Users can simulate the effect of their choices on the historical data by using the slicers, as shown in Figure 2. Imagine the simulation of a discount on orders: when the total number of items in a single order exceeds a given number of articles ( Min Quantity parameter), the Discounted Amount measure applies the Discount parameter to the transaction. If a calculation depends on multiple parameters, there could be multiple parameter tables in the model – one for each independent parameter. The main advantage of writing the calculated table manually in DAX is that it provides greater flexibility in the parameters to use. This is the approach followed in this pattern. Indeed, the Parameter feature in Power BI Desktop creates a slicer tied to a calculated table computed with the GENERATESERIES function it also creates a measure that returns the selected value of the parameter. The Parameter feature of Power BI Desktop uses a similar technique. In this pattern, we create the parameter tables by using DAX code. This chapter includes several examples with the parameter table, but this pattern has an even broader range of application. The values available for a parameter must be stored in one or more disconnected tables, which do not have a relationship with any other tables of the same model. For example, a report can show the top N products by category, letting the users decide through a slicer if they want to see 3, 5, 10 or any other number of best products. The parameter table pattern is used to create parameters in a report, so that users can interact with slicers and dynamically change the behavior of the report itself.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |