cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

## How to exclude Items from nested DAX using multiple formulas?

Current working formula I want to exclude 4 items from:

Best in Class Top 5 Average = CALCULATE(AVERAGEX(TOPN(5, SUMMARIZE(filter(all(Company),Company[Segment]="SMB"),[Company Name],"NROI", [Item Value]),[Item Value],DESC),[Item Value]),all('Global Region'))

I need to exclude 4 different Company Names from the total list of companies in the SMB Segment. I need to exclude Company A, Company B, Company C, and Company D. Right now it is taking the top 5 average from all Company Names. I need to to take the top 5 avg from all except these 4.

How do I write the syntax properly so this works? I assume something with Company Name <> "Company A", Company Name <> "Company B", etc. Not sure where to properly exclude these. Tried ALLEXCEPT but that didnt work. Stuck here on how to write this...

Thanks!!
(DAX Novice) 🙂

1 ACCEPTED SOLUTION
Super User

@jmars808 , Try like

Best in Class Top 5 Average = CALCULATE(AVERAGEX(TOPN(5, SUMMARIZE(filter(all(Company),Company[Segment]="SMB" && not( [Company Name] in {"Company A", "Company B", "Company C", "Company D"})),[Company Name],"NROI", [Item Value]),[Item Value],DESC),[Item Value]),all('Global Region'))

If you want to exclude on measure and not in other. create those two separately and then use in this formula

2 REPLIES 2
Super User

@jmars808 , Try like

Best in Class Top 5 Average = CALCULATE(AVERAGEX(TOPN(5, SUMMARIZE(filter(all(Company),Company[Segment]="SMB" && not( [Company Name] in {"Company A", "Company B", "Company C", "Company D"})),[Company Name],"NROI", [Item Value]),[Item Value],DESC),[Item Value]),all('Global Region'))

If you want to exclude on measure and not in other. create those two separately and then use in this formula

Microsoft Employee

@amitchandak Thank you!