Custom slicer parameters

A powerful use case for slicer visuals is to expose a custom list of parameter values and drive one or multiple DAX measures based on the user's selection. In the following example, a slicer visual contains six date intelligence periods and a custom DAX measure references the date intelligence measure corresponding to the user's selection:

Slicer as a measure parameter

The table used for the slicer values could be defined within a source system and retrieved during data refresh like other tables. Alternatively, since the parameter values are unlikely to change, the table could be created within Power BI Desktop and loaded to the model but not included in a data refresh. Like all parameter tables, no relationships would be defined with other tables.

The custom measure, User Selected Internet Net Sales, utilizes the SELECTEDVALUE() and SWITCH() functions to retrieve the user selection and then apply the appropriate date intelligence measure. In this implementation, a DAX variable is used to store the period selection value, per the following expression:

User Selected Internet Net Sales = 
VAR PeriodSelection = SELECTEDVALUE('Date Parameter'[Date Period Selection],"Year to Date")
RETURN
SWITCH(TRUE(),
PeriodSelection = "Week to Date", [Internet Net Sales (WTD)],
PeriodSelection = "Month to Date", [Internet Net Sales (MTD)],
PeriodSelection = "Year to Date", [Internet Net Sales (YTD)],
PeriodSelection = "Prior Week to Date", [Internet Net Sales (PWTD)],
PeriodSelection = "Prior Month to Date", [Internet Net Sales (PMTD)],
PeriodSelection = "Prior Year to Date", [Internet Net Sales (PYTD)]
)

The second parameter to the SELECTEDVALUE() function ensures that the Year to Date measure will be used if multiple values have been selected or if no values have been selected. If several additional DAX measures will be driven by the parameter selection, a dedicated measure could be created that only retrieves the selected value. This supporting measure would then eliminate the need for the variable since the support measure could be referenced directly within the SWITCH() function.

See Chapter 4, Developing DAX Measures and Security Roles for example expressions of date intelligence measures as well as measure support expressions. It's of course possible to fully define each date intelligence expression within the parameter-driven measure but, for manageability reasons, it's almost always preferable to leverage an existing measure. This is particularly the recommendation when the required measures represent common logic, such as month-to-date.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.251.206