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
)


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

 

richbenmintz's avatar
richbenmintz
Resident Rockstar
5 years ago

Hi Anonymous,

If you are able to provide a sample pbix file, I am sure parry2k or myself would be able to see your issue, pretty quickly.

 

Thanks

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    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
)


))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      It did the trick. Thanks so much!

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        5 years ago

        Glad to Help