Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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
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
Learn about visuals interactions and set yours accordingly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |