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.  In order to make this work, I have a measure, “Selected”, in a “Weeks” table.  The weeks table has a column for each of the first three predefined time periods (“full year” just returns all weeks) containing a True/False for each week if it falls within that time period: Last Week, Last 4 Weeks and YTD.  Here are the two measures at play:

Selected = SWITCH(TRUE(),

   OR(NOT(HASONEFILTER(Periods[Period])),VALUES(Periods[Period])="Full Year"),TRUE(),

        VALUES(Periods[Period])="YTD", CALCULATE(LASTNONBLANK(Weeks[YTD],0)),

        VALUES(Periods[Period])="Last 4 Weeks", CALCULATE(LASTNONBLANK(Weeks[L4],0)),

        VALUES(Periods[Period])="Last Week", CALCULATE(LASTNONBLANK(Weeks[LW],0)))

 

POS Dollars = CALCULATE(sum([POS_Dollars]),Filter(POS,Weeks[Selected]))

 

All of this works great.  Now I’ve been requested to add a separate  “Week” slicer that would supercede the “Period” slicer.  In other words, if Last 4 Weeks is selected in “Period” and that’s yeilding weeks 7-10; if a user wants to see just week 5, I want the user to be able to just select that week in the “Week” slicer without clearing the “Period” slicer (to avoid the two slicers containing mutually exclusive time periods).  To do that, I tried modifying the Selected measure to check for a filtered condition on the Week:

Selected = SWITCH(TRUE(),

   ISFILTERED(Weeks[Week]),TRUE(),

   OR(NOT(HASONEFILTER(Periods[Period])),VALUES(Periods[Period])="Full Year"),TRUE(),

        VALUES(Periods[Period])="YTD", CALCULATE(LASTNONBLANK(Weeks[YTD],0)),

        VALUES(Periods[Period])="Last 4 Weeks", CALCULATE(LASTNONBLANK(Weeks[L4],0)),

        VALUES(Periods[Period])="Last Week", CALCULATE(LASTNONBLANK(Weeks[LW],0)))

 

However, that change causes the “Periods” slicer to be ignored, yielding always the total of all POS unless a Week or several weeks are selected in the “Weeks” slicer.  This is puzzling to me because the Selected measure seems to be working as expected.  Here are some results in a simple matrix (“Weeks” unfiltered):

With original Selected measure:

With modified Selected measure:

Note the Selected measure is giving the same results, but the POS Dollars measure – which filters using the Selected measure – is not.  I’ve tried various ways of including that test for filtering on the Week in the Selected measure – always with the same result.  What am I doing wrong?

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support

    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

      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

        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