Issue with Filter Expression using calculated Object
I have a projection inside a calculation view , This Projection has Input Parameter $$IP_FiscalYear$$ which accept year from user. This Input parameter is propagated to semantic layer.
I want to restrict this projection for previous year data So I have created following two calculated column objects at projection level -
InputYearStartDate = '$$IP_FiscalYear$$ + '0101'
PreviousYear = leftstr(string(adddays(date("InputYearStartDate"),-1)),4)
Now I have created a Expression Filter for projection on dimension object FiscalYear with Filter condition defined as below to restrict previous year data-
"FiscalYear" = "PreviousYear"
I could not see the data in preview. (tested with input parameter value = 2016 to see previous year i.e 2015 data).
However I could see the data and above two calculated variables if I filter expression with Input parameter
value as - "FiscalYear" = "'$$IN_FiscalYear$$' (this will show the data for same input parameter year).
Could you please guide me whats wrong here when using calculated column in Filter Expression. Please note that all the Objects here are defined as NVARCHAR.
Thanks & Regards,
Hope I understand the problem now.
When you create input parameter using column and applying filter on the same, it gives 'No values found' in value help at data preview. This is because when you click on value help, it select values of column for which a filter has been applied i.e., the same input parameter. So this is a cyclic case, that's why no data for input parameter.
For it to be worked, you can create a static type/derived input parameter to get fiscal year values and apply as filter on column FiscalYear.