Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Andrew_MD
Regular Visitor

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

 

Andrew_MD_0-1611103980105.png

 

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

 

Andrew_MD_1-1611104073453.png

 

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

Andrew_MD_2-1611104178176.png

 

 

Table relationships

 

Andrew_MD_0-1611104423320.png

 

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!
1 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
)


))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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. 

Andrew_MD_0-1611106227284.png

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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
)


))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


It did the trick. Thanks so much!

Glad to Help



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.