Changing Measures using Field Parameters

[homeshagarwal]

Hi all!
I need your assistance creating a Time Intelligence DAX formula that will update according to the selected field parameters (sales, profit, quantity).
I wish to create a single matrix visual and give the user the ability to switch between Total Sales, Quantity, Cost etc., and view their YoY change, MoM Change, etc.

[123abc]

To create a Time Intelligence DAX formula that can dynamically switch between different measures (such as Total Sales, Quantity, Cost, etc.) based on user-selected field parameters, you can use DAX functions and a Parameter table. Here's a step-by-step guide on how to achieve this:

Step 1: Create a Parameter Table

A Parameter table is a table in your data model that holds the information about the selected measure (sales, profit, quantity, etc.). You can create it in Power BI or any other supported tool. It should have at least two columns: one for the parameter name (e.g., "MeasureName") and another for the corresponding measure column name (e.g., "MeasureColumnName"). Here's an example of what your Parameter table might look like:

MeasureName MeasureColumnName

Total Sales	Sales
Quantity	Quantity
Cost	Cost

Step 2: Create a Measure for Dynamic Calculation

You need to create a DAX measure that calculates the desired metric based on the user's selection from the Parameter table. Here's a sample DAX measure formula for YoY Change:

YoY Change = VAR SelectedMeasure = SELECTEDVALUE(Parameter[MeasureColumnName]) RETURN SWITCH( SelectedMeasure, "Total Sales", [Total Sales], "Quantity", [Quantity], "Cost", [Cost], 0 ) - CALCULATE( SWITCH( SelectedMeasure, "Total Sales", [Total Sales], "Quantity", [Quantity], "Cost", [Cost], 0 ), SAMEPERIODLASTYEAR(Calendar[Date]) )

In this example, we use the SWITCH function to dynamically select the appropriate measure based on the value selected in the "MeasureName" column of the Parameter table. We then calculate the YoY change for the selected measure.

Step 3: Create a Matrix Visual

Create a matrix visual in your report where you want to display the dynamic time intelligence measures.

Step 4: Add Parameter to the Matrix Visual

Drag the "MeasureName" column from your Parameter table to the rows or columns of the matrix visual. This will allow users to select the desired measure (e.g., Total Sales, Quantity, Cost) dynamically.

Step 5: Add the Dynamic Measure to the Matrix

Drag the "YoY Change" measure you created in Step 2 to the Values area of the matrix visual.

Now, when users select a measure from the "MeasureName" column in the matrix, the "YoY Change" measure will dynamically calculate and display the YoY change for the selected measure.

Repeat the process for other time intelligence calculations like MoM Change, QoQ Change, etc., by creating similar DAX measures for those calculations.

By following these steps, you can create a dynamic report that allows users to switch between different measures and view their time-based changes using DAX formulas and a Parameter table.

[123abc]

In Power BI, you can create a dynamic measure that changes based on selected field parameters (like sales, profit, quantity) using DAX and field parameters. To achieve this, you can follow these steps:

1. Create Measures for Different Metrics: First, create individual measures for each metric you want to analyze, such as Total Sales, Quantity, and Cost. For example, create measures like Total Sales, Total Quantity, and Total Cost.

2. Create a Parameter Table: Create a parameter table in Power BI that allows users to select the metric they want to analyze. This table should have at least two columns: one for the parameter name (e.g., "Metric") and another for the values (e.g., "Total Sales," "Total Quantity," "Total Cost").

3. Create a Slicer for Metric Selection: Add a slicer visual to your report based on the parameter table's "Metric" column. This slicer will allow users to switch between metrics.

4. Create a Dynamic Measure Using SWITCH: Now, create a dynamic DAX measure that changes based on the selected metric. You can use the SWITCH function to achieve this. Here's an example DAX formula for a YoY change measure:

Selected Metric YoY Change =
VAR SelectedMetric = SELECTEDVALUE(ParameterTable[Metric])

RETURN
SWITCH (
SelectedMetric,
"Total Sales", [Total Sales YoY Change],
"Total Quantity", [Total Quantity YoY Change],
"Total Cost", [Total Cost YoY Change],
BLANK()
)

1. In this formula, it checks the selected metric using the SWITCH function and returns the corresponding YoY change measure. Adjust this formula for other time intelligence calculations like MoM change, QoQ change, etc., and for different metrics.

2. Add the Dynamic Measure to Matrix Visual: Finally, add the dynamic measure (Selected Metric YoY Change or similar) to your matrix visual. When users select a different metric using the slicer, the measure will automatically update based on their selection.

Now, users can use the slicer to switch between different metrics (sales, quantity, cost) and view their YoY changes, MoM changes, etc., in the same matrix visual.