Measure not affected by month slicer

[Anonymous]

Hi All,

i want to create set of measures which do not affected by month slicer. Means when i apply month filter, measures "Avg_Q1'21" & "Avg_Q4'19" should not get affected after applying month slicer only and along with that under "Current_MonthSales" it should return the value as per month slicer.

For example:

As per below screenshot (1), when i didn't apply any month filter values of "Avg_Q1'21" & "Avg_Q4'19" are showing but "Current_MonthSales" is blank.

As per below screenshot (2), when i apply any month filter values of "Avg_Q1'21" & "Avg_Q4'19" are not showing but value of "Current_MonthSales" is showing.

Solution Required: Once i apply month slicer, values of  "Avg_Q1'21" & "Avg_Q4'19" should not get affected and along with that some values should visible for "Current_MonthSales".

Note: I have applied selectedvalue function for "Current_MonthSales" measure.

Power BI working file & Raw Data Link: https://drive.google.com/drive/folders/1X1b5AJRLRH5OEjzhanjsO9CjfYcPmDAL?usp=sharing

Thanks in advance 🙂

[v-shex-msft · Accepted solution]

Hi @Anonymous,

By default 'selectedvalue' function not handle multiple selections, you need to add the 'alternate Result' expression to handle this.

SELECTEDVALUE function - DAX | Microsoft Docs

Current_MonthSales =
VAR selectedvalue =
    SELECTEDVALUE ( Date_tbl[Month], MAX ( Date_tbl[Month] ) )
RETURN
    CALCULATE ( SUM ( Data[Sales] ), Date_tbl[Month] = selectedvalue )

For the two average measures, I found you add static filters in it so it shows blank when no records matched with current filters.

If you do not want to interact with the current slicer effect, you can add all functions to ignore fitelrs.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

Regards,

Xiaoxin Sheng

[lbendlin]

Learn about visuals interactions and set yours accordingly.