Best Practices for Multiple Variables

[Alex-PBIComm]

Hello everyone,

I'd like to post a general question to know if there's a defined best practice when multiple variables have to be considered in some measures. 

For example, I currently use a "double switch" to calculate sales, because the customer wants to be able to see numbers with or without VAT, and in local currency or converted amount. In this case we're covering two variables: VAT and Currency. What if there are more variables? In that case I would have to hardcode 8/16/32 different possible outcomes. 
An example of a measure with two variables, where on screen I have two slicers (_Conversion_Slicer[Conversion], _vat_slicer[vat]):

sales_full_date =
if(VALUES(_Conversion_Slicer[Conversion]) = "Local Currency",
Switch( TRUE(),
    VALUES(_vat_slicer[vat]) = "W/o VAT",Calculate(sum(dl_global_sell_outs_datamart[amount_excl_vat]),
        dl_global_sell_outs_datamart[close_date]<=MAX('Calendar'[Date]),
        dl_global_sell_outs_datamart[close_date]>=Min('Calendar'[Date])),
    VALUES(_vat_slicer[vat]) = "With VAT",  Calculate(sum(dl_global_sell_outs_datamart[amount]),
        dl_global_sell_outs_datamart[close_date]<=MAX('Calendar'[Date]),
        dl_global_sell_outs_datamart[close_date]>=Min('Calendar'[Date])),
    Blank()),

Switch( TRUE(),
    VALUES(_vat_slicer[vat]) = "W/o VAT",Calculate(sum(dl_global_sell_outs_datamart[amount_excl_vat_global_fx_chf]),
        dl_global_sell_outs_datamart[close_date]<=MAX('Calendar'[Date]),
        dl_global_sell_outs_datamart[close_date]>=Min('Calendar'[Date])),
    VALUES(_vat_slicer[vat]) = "With VAT",  Calculate(sum(dl_global_sell_outs_datamart[amount_global_fx_chf]),
        dl_global_sell_outs_datamart[close_date]<=MAX('Calendar'[Date]),
        dl_global_sell_outs_datamart[close_date]>=Min('Calendar'[Date])),
    Blank()))
 

Having "only" two variables I have to cover 4 possible outcomes, but I believe that's the very limit as with 3 variables the number goes up to 8 already and it doesn't feel like proper design.
I know that we could split the reports by topic and have measures focused on certain outcomes only, but in this scenario let's pretend that the customer wants the freedom to select any of the variables and see the numbers changing in the same box.

Thank you!

[Adamboer]

When dealing with multiple variables in measures, it is important to consider the scalability and maintainability of the code. Hardcoding all possible outcomes can quickly become unmanageable and difficult to modify as requirements change or new variables are introduced.

One approach to handle multiple variables is to use a dynamic calculation that can adjust based on the user's selections. For example, instead of hardcoding all possible outcomes, you could create a calculation that dynamically selects the appropriate fields based on the user's selection. This could be achieved by using a combination of SWITCH and SELECTEDVALUE functions.

Another approach is to use a parameter table that holds the possible values of each variable and their corresponding fields. This table can then be used to dynamically select the appropriate fields and values for the calculation. This approach can provide more flexibility and scalability as new variables can be easily added to the parameter table without the need to modify the code.

In general, it is recommended to use a modular approach when dealing with complex calculations involving multiple variables. This allows for better organization and maintainability of the code. Additionally, using clear and descriptive variable names can make the code more readable and easier to understand.