Max measure can't be used as condition in another measure?

[lauriemclolo]

I had a measure for max current quarter as

Max Current Yr Qtr = format(MAXX(FILTER(v_tcp_enrollment,v_tcp_enrollment[ag_level]="QTR"&& v_tcp_enrollment[year_group]="TY"), v_tcp_enrollment[fiscal_nbr_in_year]),"string")

However, even though the max returns the value "2", when I use it in the formula for Enrolled Count QTD (formula below) it adds up the totals for both quarter 1 and quarter 2.  I had to switch the measure Max Current Yr Qtr to a calculated column instead of a measure in order for the formula below to work.  I don't want a column, because now I have a column reserved to hold the number "2" on every row.    Is there an easier way to to this?

Enrolled Count QTD = IF(v_tcp_enrollment[ag_level] = "QTR" && v_tcp_enrollment[year_group] = "TY" && format(v_tcp_enrollment[fiscal_nbr_in_year],"") = v_tcp_enrollment[Max Current Yr Qtr],v_tcp_enrollment[enrolled_count],0)

[v-ssriganesh · Accepted solution]

Hello @lauriemclolo,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your scenario in Power BI Desktop using sample data that mirrors your v_tcp_enrollment table, and I was able to achieve the expected output: summing the enrolled_count only for the maximum quarter (2) where ag_level = "QTR" and year_group = "TY", without using a calculated column.

The issue occurs because the original Enrolled Count QTD measure doesn’t correctly apply the max quarter filter in the row context. To resolve this, I’ve created two measures:

• Max Current Yr Qtr: Calculates the maximum fiscal_nbr_in_year for the specified filters.
• Enrolled Count QTD: Uses a variable and CALCULATE to sum enrolled_count only for the max quarter.

Here are the DAX measures:

Max Current Yr Qtr = FORMAT( MAXX( FILTER( v_tcp_enrollment, v_tcp_enrollment[ag_level] = "QTR" && v_tcp_enrollment[year_group] = "TY" ), v_tcp_enrollment[fiscal_nbr_in_year] ), "0" )

Enrolled Count QTD = VAR MaxQtr = MAXX( FILTER( ALLSELECTED(v_tcp_enrollment), v_tcp_enrollment[ag_level] = "QTR" && v_tcp_enrollment[year_group] = "TY" ), v_tcp_enrollment[fiscal_nbr_in_year] ) RETURN CALCULATE( SUM(v_tcp_enrollment[enrolled_count]), v_tcp_enrollment[ag_level] = "QTR", v_tcp_enrollment[year_group] = "TY", v_tcp_enrollment[fiscal_nbr_in_year] = MaxQtr )

• Max Current Yr Qtr: Returns "2" (the maximum fiscal_nbr_in_year for ag_level = "QTR" and year_group = "TY").
• Enrolled Count QTD: Uses a variable to compute the max quarter and CALCULATE to sum enrolled_count only for rows matching ag_level = "QTR", year_group = "TY", and the max quarter (2). This avoids the need for a calculated column.

I’ve attached a sample .pbix file that demonstrates the solution with the sample data and visuals (a card showing Max Current Yr Qtr = 2 and Enrolled Count QTD = 150, plus a table to verify the results).

Thank you, @Shravan133 for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[lauriemclolo]

Thank you for your help.  I accepted as solution though I was able to do without the allselected . Is there a reason why I need to add "allselected" in the equation? Here is my version:

Enrolled Count QTD =
VAR MaxQtr =
MAXX(
FILTER(
v_enrollment,
v_enrollment[ag_level] = "QTR"
&& v_enrollment[year_group] = "TY"
),
v_enrollment[fiscal_nbr_in_year]
)
RETURN
calculate(sum(v_enrollment[enrolled_count]),v_enrollment,v_enrollment[fiscal_nbr_in_year]=MaxQtr,v_enrollment[year_group]="TY",v_enrollment[ag_level]="QTR")

[Shravan133]

Try this: 

Enrolled Count QTD =
VAR MaxQtr =
MAXX(
FILTER(
v_tcp_enrollment,
v_tcp_enrollment[ag_level] = "QTR"
&& v_tcp_enrollment[year_group] = "TY"
),
v_tcp_enrollment[fiscal_nbr_in_year]
)
RETURN
IF(
v_tcp_enrollment[ag_level] = "QTR"
&& v_tcp_enrollment[year_group] = "TY"
&& v_tcp_enrollment[fiscal_nbr_in_year] = MaxQtr,
v_tcp_enrollment[enrolled_count],
0
)

[lauriemclolo]

This didn't work with the if statement.  Not sure why.