Forum Discussion

cnewell7's avatar
cnewell7
Icon for Helper II rankHelper II
8 years ago

DAX respect only one slicer

Dear All

 

Here is the situation:

 

I have two slicers allowing a user to select Month (MonthLong) & Year respectively. Both are columns in my calendar table

 

Without selecting any particular months in the month slicer the DAX expression below works fine. I get a value returned which is at a year level

 

However, if I select a Month I would expect the measure to respect the month filter being applied to the calendar table. It is does not and I just get the same figure at year level

 

Have gone about constructing the expression with best intentions but obviously missing something, help much appreciated

 

 

LY ACTUAL =
VAR LY_VAL = [LY] // derive LY value from users selection of year
RETURN
CALCULATE (
[PL_TOTAL],
DIM_DTYPE[DTYPE] = "ACT",
FILTER(ALLEXCEPT(DIM_CALENDAR, DIM_CALENDAR[MonthLong]), DIM_CALENDAR[Year]= LY_VAL )
)

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Sample data to match up with your formula would be very beneficial.

    • cnewell7's avatar
      cnewell7
      Icon for Helper II rankHelper II
      8 years ago

      I've actually found a workaround to what I needed (DATEADD) but am mystified as to why this doesn't work

       

      I'm basically saying "don't disregard the selection in the month slicer' but it is doing. So the question would be why is ALLEXCEPT not doing what its supposed to ?

       

      If you want to skype me it would be easier to show you

       

      PS the reason I hold the value of a measure in a variable is because if i use the measure directly in my filter expression it doesn't work

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi cnewell7,

         

        Could post your table structures(including the relationships) with some sample/mock data which can help us to reproduce this issue, so that we can help further investigate on it? It's better that you can share a sample pbix file(with just some sample/mock data) which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

         

        Regards