Forum Discussion

J_Ech's avatar
J_Ech
Frequent Visitor
7 years ago

Superseding one slicer with another

I have a measure that allows total retail sales (POS) to be displayed by one of four predefined time ranges selected from a “Period” slicer: last week, last four weeks, year-to-date and full year.  I...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, J_Ech 

You may try this formula:

Selected_New = IF(ISFILTERED(Weeks[Week]),TRUE(),SWITCH(TRUE(),
      OR(NOT(HASONEFILTER(Periods[Period])),VALUES(Periods[Period])="Full Year"),TRUE(),
        VALUES(Periods[Period])="YTD", TOPN(1,LASTNONBLANK(Weeks[YTD],0)),
        VALUES(Periods[Period])="Last 4 Weeks",TOPN(1,LASTNONBLANK(Weeks[L4],0)),
        VALUES(Periods[Period])="Last Week", TOPN(1,LASTNONBLANK(Weeks[LW],0))))

Result:

before filter weekafter filter week

 

Best Regards,

Lin

 

J_Ech's avatar
J_Ech
Frequent Visitor
7 years ago

Thank you for the response; unfortunately, either way, the "Period" slicer is then ignored even when there are no selections on the "Week" slicer -- and even if the "Week" slicer does not exists on the page.  I'm trying to still use the Period slicer, but if something is selected on Week, having that selection supersede the Period selection (to avoid having to instruct the user to clear Period in order to use Week).  I would expect your solution to work that way (just as I expected my attempts to work), but it does not.