Issue with Filter Expression using calculated Object

[Former Member]

Dear Experts,

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,

Jiten

[Former Member · Accepted solution]

Hi Jiten,

1. Is your value help of input parameter $$IP_FiscalYear$$  listing down the values  at data preview?

2. Are you creating input parameter using column FiscalYear and applying the filter on same column?

Can you attach screenshots of your model if possible?

Regards,

Shireesha

[Former Member · Accepted solution]

Hi Shireesha,

Thank you for looking into this.

1. I do not see the values on value help window but there is data available for sure for Year 2016   and   2015. since I could retrieve the data for these years if I apply filter by direct "FiscalYear" = "$$IP_FiscalYear$$" instead of using FiscalYear = "PreviousYear".

2. Yes.

Here are the screenshots-

Thanks & Regards,

Jiten

[Former Member · Accepted solution]

Hi Jiten,

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.

Regards,

Shireesha

[Former Member · Accepted solution]

Thank you Shrieesha !

You explained well for the value help at data preview. This given me clue to recheck my whole model again. Also I found that my model was using a root level reuse view which had expression filter for current Year and that was filtering data at root level itself to fetch only current year data. Hence on top node it was not displaying data for previous Year filter. I somehow missed it since my complex model was using many reuse views at many level.

I will be more careful next time while debugging.

I fixed it by modifying root level reuse view expression filter (of PreviousYear projection) to use calculated object PreviousYear.

Regards,

Jiten