Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Allow indirect filter using allexcept

Hi Everyone,   Im trying to calculate the previous months sum of records but allow filtering on an attribulte (HomeRegion). I have a master table of HomeRegions joined to the enquiries table and wo...
Need Help
Tips and Tricks
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi Anonymous,

     

    This should do the trick

    CN_New Enquiries in past Month 2 = if(
max(DateMonthTable[YYYYMM])
>
format(
today(),
"yyyyMM"
),
0,
CALCULATE(
sum(CN_Enquiries[Number Of Enquiries]),
filter( 
    ALLEXCEPT(CN_Enquiries,'Teams'[Master HomeRegion]),
datediff( CN_Enquiries[EOM_EnquiryDate],
max(DateMonthTable[End of Month]),
MONTH)
= 1
)


))
parry2k's avatar
parry2k
Super User
5 years ago

Anonymous I would recommend to post sample data and expected output rather than sharing DAX, sometime it is hard to understand DAX expression without looking at the data and the expected output.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for the feedback parry2k. I've added some context around the visuals. Hopefully that helps

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi Anonymous 

       

      Should you not change your measure to the following, adding the filter table to the allexcept filter condition in the calculate

       

      CN_New Enquiries in past Month = if(
max(DateMonthTable[YYYYMM])
>
format(
today(),
"yyyyMM"
),
0,
CALCULATE(
sum(CN_Enquiries[Number Of Enquiries]), ALLEXCEPT('Teams'[Master HomeRegion]),
filter( ALLEXCEPT(CN_Enquiries,CN_Enquiries[HomeRegion]),
datediff( CN_Enquiries[EOM_EnquiryDate],
max(DateMonthTable[End of Month]),
MONTH)
= 1
)))

       

      Hope this helps,

      Richard

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi richbenmintz 

         

        Thanks alot for having a look. I did try things like this but i still get the same result. I just created a new measure with your suggested code and still the filter is being ignored.