Forum Discussion
Validate Slicer Selection
Hello I have a period column in a table that I am using as a slicer. It is of type date and I am using it as a Date Hierarchy. I need to validate if the user selected the same number of months ...
Anonymous here you go, add following measure
Month Selected = VAR __year = SUMMARIZE ( 'Calendar', [Year], "@Months", CONCATENATEX ( VALUES ( 'Calendar'[Month] ), [Month], "," ) ) VAR __status = IF ( COUNTROWS ( VALUES ( 'Calendar'[Year] ) ) = 1, -1, COUNTX ( SUMMARIZE ( __year, [@Months], "@Rowcount", COUNTROWS ( __year ) ), [@Rowcount] ) ) RETURN SWITCH ( __status, 1, "Valid Selection", -1, "Invalid Selecton - Only months selected in one year", "Invalid Selection - not same months selected" )Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
count months : =
COUNTROWS(VALUES('Calendar'[Month Name]))
count years : =
COUNTROWS(VALUES('Calendar'[Year]))
Selection Validate Measure =
VAR monthscount =
COUNTROWS ( VALUES ( 'Calendar'[Month Name] ) )
VAR yearscount =
COUNTROWS ( VALUES ( 'Calendar'[Year] ) )
VAR newtablemonth =
ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Month] ), "@countyears", [count years :] )
VAR newtableyear =
ADDCOLUMNS (
ALLSELECTED ( 'Calendar'[Year] ),
"@countmonths", [count months :]
)
RETURN
IF (
monthscount = MINX ( newtableyear, [@countmonths] )
&& yearscount = MINX ( newtablemonth, [@countyears] ),
"Months selected correctly",
"Months missing from selection"
)
thank you for your reply. your solution only works if i select something in both years. if i only select in one of the years, it is no longer working and this is a scenario that would happen often. i appreciate your suggestion