DAX Formula for Parameterized Value Display

Objective

To create a DAX measure that retrieves and displays a value based on a specified parameter.

Formula

To implement this, you can utilize the SWITCH function along with a parameter, as follows:

ValueBasedOnParameter = 
SWITCH(
    TRUE(),
    SelectedParameter = "Option1", Measure1,
    SelectedParameter = "Option2", Measure2,
    SelectedParameter = "Option3", Measure3,
    BLANK()  // Default case if no options match
)

Explanation

1. SWITCH Function: This function evaluates a condition and returns a value corresponding to the first condition that is true.

2. TRUE() Function: Used to evaluate multiple conditions based on the SelectedParameter so that each condition can be checked.

3. Conditions:

   • SelectedParameter: This represents the parameter input by the user, which can be a column that holds specific options (like "Option1", "Option2", etc.).
   • Measure1, Measure2, Measure3: These are the measures (or calculations) you want to show based on the selected option. Replace these with your actual measure names.

4. BLANK(): This is the default value returned if none of the conditions are met, ensuring a clean output.

Practical Example

Assume you have a sales report where you want to show sales data based on different regions selected from a parameter slicer:

Scenario Description

• Parameter: SelectedRegion (Options: "North", "South", "East", "West")
• Measures:
  • NorthSales: Total sales for the North region
  • SouthSales: Total sales for the South region
  • EastSales: Total sales for the East region
  • WestSales: Total sales for the West region

Implementation

SalesByRegion = 
SWITCH(
    TRUE(),
    SelectedRegion = "North", [NorthSales],
    SelectedRegion = "South", [SouthSales],
    SelectedRegion = "East", [EastSales],
    SelectedRegion = "West", [WestSales],
    BLANK()  // No matching region
)

Explanation of Example

1. The SalesByRegion measure dynamically returns the total sales based on the user's selection in the SelectedRegion parameter.
2. Each condition checks the selected region and retrieves the corresponding sales measure.
3. If no valid region is selected, it returns a blank, ensuring no erroneous outputs are displayed.

Conclusion

This DAX measure is a powerful tool for dynamically displaying values based on user input, enhancing the interactivity of your reports. For further learning and enhancement of your DAX skills, consider exploring courses on the Enterprise DNA Platform.