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. 

       

      • 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