Forum Discussion

nick9one1's avatar
nick9one1
Helper III
3 years ago
Solved

Strange behaviour using metric vs hard coded value

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!

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    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)
    )

     

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    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)
    )