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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Chocapsuro
New Member

Double filter in same table/column

Hi,

 

I have an Excel File with a list of aircraft with the main characteristics of each one (airline, model, manufacturer, age...). I want to identify, per model, which is the position of each aircraft per age: e.g. for all A320 if aircraft X is 19.8 years old, how many aircraft are older than it (that is the order of age filtered by model). I wouldn't like to use an auxiliary table as I want to further elaborate on this table.

When it comes to calculate the number of aircraft per model I use the following formula:

 

Aicraft per Model = CALCULATE(COUNT(Table 'Aicraft Name'), ALLEXCEPT ( Table, Table 'Aicraft Model'))

 

And it works, but I do not know how to calculate the next step, that would be the number of aircraft within the list that are older than the row/aircraft that is evaluated. I have tried the following without success:

 

Aicraft Age Order = CALCULATE(COUNT(Table 'Aicraft Name'), FILTER( ALLEXCEPT ( Table, Table 'Aicraft Model') , Table 'Aicraft Age' > Table 'Aicraft Age' ))

 

Could someone help me with this? Thank you very much!

 

Chocapsuro

 

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@Chocapsuro Try:

Aircraft Older Than Measure =
  VAR __Model = MAX('Table'[Aircraft Model])
  VAR __Age = MAX('Table'[Aircraft Age])
  VAR __Table = FILTER(ALL('Table'),[Model] = __Model && [Aircraft Age] > __Age)
RETURN
  COUNTROWS(__Table)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@Chocapsuro Try:

Aircraft Older Than Measure =
  VAR __Model = MAX('Table'[Aircraft Model])
  VAR __Age = MAX('Table'[Aircraft Age])
  VAR __Table = FILTER(ALL('Table'),[Model] = __Model && [Aircraft Age] > __Age)
RETURN
  COUNTROWS(__Table)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ! Using variables it works perfectly, thank you very much!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.