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

Lin – I see from your examples that your solution does work for you; however, when I use the exact same solution (copied and pasted from your post) in the exact same file, the Period filter simply does not work – rather, all weeks are selected unless the Week filter is applied.  Here's what I have: 

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))))
POS Quantity New = CALCULATE(sum([POS_Quantity]),Filter(POS,Weeks[Selected_New]))

Yet here are my results:
No Week FilterWeek Filter

Any idea what may cause this behavior??  Why would I get different results from you?