Strange behaviour using metric vs hard coded value

[nick9one1]

I have a table that lists bookings by location, academic year, financial year, financial week. 

I'm trying to count the number of rows for the lastest financial week and year entry.

Booked Rooms 4 = CALCULATE (
    COUNTX ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[LettingsFactKey] )
    ,FILTER ( 'Lettings DIM_ReservationType', 'Lettings DIM_ReservationType'[ReservationTypeKey] = 1 )
    ,FILTER ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[FinancialWeek] = [Max Week])
    ,FILTER ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[FinancialYear] = [Max Year])
    )

Max Week = MAX('Lettings FACT_LettingsWeekly'[FinancialWeek])

Max Year = MAX('Lettings FACT_LettingsWeekly'[FinancialYear])

 
you can see here that the total is 374, which is incorrect. It should be 163

When I change the measure to use '33' for week number instead of [Max Week] it's correct. 

why is this?

Thanks!

[nick9one1]

Thats done it 🙂 thank you!

[jdbuchanan71 · Accepted solution]

@nick9one1 

I think you are getting caught by the measures [Max Week] and [Max Year] getting calculated in the context of theCOUNTX iteration.  Try it like this, where the value is set outside the iteration and used inside it.  Does that help?

Booked Rooms 4 = 
VAR _MaxWeek = [Max Week]
VAR _MaxYear = [Max Year]
RETURN
CALCULATE (
    COUNTX ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[LettingsFactKey] )
    ,FILTER ( 'Lettings DIM_ReservationType', 'Lettings DIM_ReservationType'[ReservationTypeKey] = 1 )
    ,FILTER ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[FinancialWeek] = _MaxWeek)
    ,FILTER ( 'Lettings FACT_LettingsWeekly', 'Lettings FACT_LettingsWeekly'[FinancialYear] = _MaxYear)
    )