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 would like to use that as the filter. Which would usually indirectly filter the Enquieries table. The ALLEXCEPT function is ignoring the indirect filter. 

 

Is there anyway to allow indirect filtering for a field within a filter&all statement?

 

The unfiltered visual. which is working correctly

 

 

The visual when filtered directly on home region. This is the output im after

 

 

The visual when indirectly filtered. This happens when i filter using the homeregion master table that is joined to the enquireies table

 

 

Table relationships

 

 

Dax Below

 

CN_New Enquiries in past Month = if(
max(DateMonthTable[YYYYMM])
>
format(
today(),
"yyyyMM"
),
0,
CALCULATE(
sum(CN_Enquiries[Number Of Enquiries]),
filter( ALLEXCEPT(CN_Enquiries,CN_Enquiries[HomeRegion]),
datediff( CN_Enquiries[EOM_EnquiryDate],
max(DateMonthTable[End of Month]),
MONTH)
= 1
)))
 
Thanks!
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
)


))

8 Replies

  • 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