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

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. 

     

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


))