Forum Discussion
nick9one1
Helper III
Helper IIIStrange 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. Booke...
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) )
jdbuchanan71
Super User
Super UserI 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)
)