Allow indirect filter using allexcept

[Andrew_MD]

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!

[parry2k]

@Andrew_MD 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.

[Andrew_MD]

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

[richbenmintz]

Hi @Andrew_MD 

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

[Andrew_MD]

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]

Hi @Andrew_MD,

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 · Accepted solution]

Hi @Andrew_MD,

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
)


))

[Andrew_MD]

It did the trick. Thanks so much!

[richbenmintz]

Glad to Help