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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
AABright
Frequent Visitor

CALCULATE with a filtered filter

Hello everyone, I am trying to use the calculate function with multiple filters. The goal is to calculate the Score of the highest Run per year.

It's currently hardcoded like this, 

Score2020 = calculate([Score],Scores[year]=2020,Scores[run]=12) [results 0.62] 

Score2021 = calculate([Score],Scores[year]=2021,Scores[run]=13) [results 0.63]

Score2022 = calculate([Score],Scores[year]=2022,Scores[run]=2) [results 0.18]

 

But I don't want to manually enter in the Run for Score2022 as the year progresses. 

This is one of my many attempts, but I get errors every time.

Score2022.2 = calculate(Scores[Score],KEEPFILTERS(and(Scores[Year]=max(Scores[Year]),LOOKUPVALUE(Scores[Run],Scores[Year],max(Scores[Year])))))
 
I've also tried referning a separate calculation:
Score2022.3 = calculate([Score] ,Scores[Year]=2022Scores[Run] 'Calculations'[CurrentRun 2022])
 this references this calculation; CurrentRun 2022 = calculate(max(Scores[Run]),Scores[Year]=2022)
But this errors out as well.
 
How can I get this done?
 

AABright_1-1647959632261.png

1 REPLY 1
johnt75
Super User
Super User

If you need a separate measure for each year then you could try

Score 2022 = CALCULATE( MAX(Scores[Score]), Scores[Year] = 2022 )

Or you could have it in 1 measure as

Best score = MAX( Scores[Score])

If you use a slicer on Scores[Year], or put Scores[Year] into a visual then it will filter the results for each year separately.

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.