This will be very technical, and just a “resting place” for some code that took a while to make work. If it doesn’t make sense, then I apologize to all the non-code folks who occasionally stop by.
While working on a new PowerPivot report for the Boy Scouts, I needed to find out the number of Roundtables a District had entered into the Commissioner Tools in order to determine the percent of Roundtables a unit had attended. This was my solution.
Formula for calculating the maximum number of RT which have been recorded in CT by a District: RT:=CALCULATE(MAX([Num_RT_Attended]),FILTER(RT_Merge,[District Name]=EARLIER([District Name]))) Where ... [Num_RT_Attended] - must be a column in the data model [District Name] - must be a column in the data model RT_Merge = the name of the data model [Num_RT_Attended]:=if([Aug]>0,1,0)+if([Sep]>0,1,0)+if([Oct]>0,1,0)+if([Nov]>0,1,0)+if([Dec]>0,1,0)+if([Jan]>0,1,0)+if([Feb]>0,1,0)+if([Mar]>0,1,0)+if([Apr]>0,1,0)+if([May]>0,1,0)+if([Jun]>0,1,0)+if([Jul]>0,1,0)
[Num_RT_Attended] ~ (# RT Attended) [PCT] ~ (% RT Attended):=Divide([Num_RT_Attended],[RT])
Fun stuff, eh!?! Of course, you can take the same code and apply it to many situations where you are trying to find the maximum number by a specific filter in the CALCULATE() function of DAX. One thing to remember, the MAX() function must be working on a column of data, it cannot work on a DAX Measure.