Forum Discussion

CherC's avatar
CherC
Frequent Visitor
2 years ago

Select measure based on parameter selection

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?  

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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?