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 

Could you please share your sample pbix file for us. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading

 

Best Regards,

Lin

J_Ech's avatar
J_Ech
Frequent Visitor
7 years ago

Here's a link to a sample dummy file: https://1drv.ms/u/s!Ah2jZnGaefGeaiEzimg8s6YRglI

 

I've created a measure "Selected_New"; if you replace the "Selected" measure with that one in either the "POS Quantity" or "POS Dollars" measures, you'll see the problem I'm working on.  Thank you.

  • 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. 

    • 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?