Select measure based on parameter selection

CherC — Tue, 20 Aug 2024 00:26:38 GMT

I have a parameter I created to enable a dynamic date axis on a matrix table. This allows the user to select the date intervals, (for a date range selected elsewhere), which shows the intervals as the column headers. It calculates the totals correctly.

Axis = {

("Year", NAMEOF('dates'[Year]), 0),

("Quarter", NAMEOF('dates'[Quarter]), 1),

("Month", NAMEOF('dates'[Month]), 2),

("Week", NAMEOF('dates'[Week]), 3)

}

However, in addition to the totals values, I also have 4 change% measures which calculate the change % over the previous period totals for each of the intervals listed above.

I have 4 change % measures for: year/year, month/month, ... How can I either: 1) write a measure to select the appropriate %change measure based on the name of the selection chosen from the parameter slicer, or 2) write one measure that will select the appropriate calculation based on the name of the selection chosen from the parameter slicer?

Re: Select measure based on parameter selection

OwenAuger — Tue, 20 Aug 2024 03:08:01 GMT

Hi CherC

Yes, you can create a "Period-over-Period" measure that is conditional on the Axis selection.

Here are some examples:

1. If you have already defined measures Year/Year % etc:

Sales Period-over-Period % = VAR AxisSelection = SELECTCOLUMNS ( 'Axis', 'Axis'[Axis] ) VAR Result = IF ( COUNTROWS ( AxisSelection ) = 1, -- Single parameter selection SWITCH ( AxisSelection, "Year", [Year/Year %], "Quarter", [Quarter/Quarter %], "Month", [Month/Month %], "Week", [Week/Week %] ) ) RETURN Result

2. If you want to include the calcs all within a single measure (assuming the underlying measure is Sales) :

Sales Period-over-Period % = VAR AxisSelection = SELECTCOLUMNS ( 'Axis', 'Axis'[Axis] ) VAR Result = IF ( COUNTROWS ( AxisSelection ) = 1, -- Single parameter selection VAR ValueCurrentPeriod = [Sales] VAR ValuePreviousPeriod = SWITCH ( AxisSelection, "Year", CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'dates'[Date] ) ), "Quarter", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -1, QUARTER ) ), "Month", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -1, MONTH ) ), "Week", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -7, DAY ) ) ) RETURN DIVIDE ( ValueCurrentPeriod - ValuePreviousPeriod, ValuePreviousPeriod ) ) RETURN Result

Does something like this work for you?

topic Re: Select measure based on parameter selection in DAX Commands and Tips

Select measure based on parameter selection

Re: Select measure based on parameter selection