a function calculate has been used in a true/false expression that is used as a table filter expression which is not allowed

4
votes

I have a to check the current month and current year value for a measure in ssas tabular model but due to type mismatch i am not able to do this. For this, i have created a measure in which i am using this dax query 

:CurrMonthYear:=CONCATENATE(CONCATENATE("""",concatenate(year(now()),CONCATENATE(0,month(now())))),"""")       output: "201704"

...to calculate currentyear and currentmonth. But when i give this value in a measure like this:

SumOfRevisedForecast:=CALCULATE(SUM(DimRevisedForecast[RevisedForecast]),DimRevisedForecast[Approved] <>"N"  && DimRevisedForecast[Approved] <>blank(),'DimRevisedForecast'[CalendarYearMonth] =CurrMonthYear)

...this doesnt work. Though, giving "201704" in place of CurrMonthYear works.

Can anybody help me in this?

Thanks in advance

1
sql-serverssasdaxssas-tabular

1 Answers

12
votes

The problem is not with CurrMonthYear measure, it's with your second formula - CALCULATE function does not accept measures as criteria, only values. That's why it works when you put "201704" explicitly but fails when you use the measure.

A common solution is to wrap the criteria into FILTER function, something like:

CALCULATE(SUM(DimRevisedForecast[RevisedForecast]), 
          FILTER ('DimRevisedForecast',  
                  'DimRevisedForecast'[CalendarYearMonth] = [CurrMonthYear])

A great explanation of this issue is here: FILTER() – When, Why, & How to Use It