SSAS - When my "calculated column" changes its values, the filter remain with old ones

[Anonymous]

Hi,

I have a SSAS Multidimensional with a live connection to my Power BI Desktop.

I have created a "calculated column" in SSAS to have always current month selected automatically. It's the classic "Current Month" column that you use into a filter and that changes his values if the month changes.

Conceptually:

--- IF [Year - Month] is the current month THEN write "Current Month" ELSE wirte [Year - Month] --- 

 (picture of October)

Here the problem:

Yesterday the month changes and my filter should remain the same "Current Month" but it strangely changes (automatically) to October:

(picture of 2 November)

Somebody can explain me this behavior?

I always use this kind of solution for current month and this is the first time that I have this issue.

Thank you.

Best

[sdjensen]

@Anonymous - where do you experience this issue? In PBI Desktop or in the service? If it's in the service it might be because another user edited the report and save it with the filter set to October?

I do a similar thing when creating date tables for Power BI / Tabular models. But instead of renaming the current month I create a new column that will have the values "This Month, This Month -1, This Month -3....." and call it Dynamic Month and I never experienced any issues with this.

[Anonymous]

@sdjensen,

I experience the issue both in Power BI Desktop and Service. Moreover, I'm the only one with access to the account that can modify reports.

Maybe, with the SSAS Multidimensional, Power BI act with different behaviors..

[sdjensen]

@Anonymous - would you be able to make changes to the cube? if so you could try to create this value in the cube instead of as a calculated column.

[Anonymous]

@sdjensen,

As I know, with Live Connection on SSAS Multidimensional is not possible to create any DAX, (neither column or measure), so I can only make changes on the cube.

If I'm wrong and is possible to use DAX on this connection, please tell me how because it will help me in a lot of issues.

Thank you!

[sdjensen · Accepted solution]

@Anonymous - I think I might have an idea to what could be causing this then.

In a multi-dimensional cube a attribute (single level user defines hierarki) can have both a key column and name column, if you change the value in the name column, but the key column is the same (guess you will need this to keep the correct sort order) this could be the problem. Because I would suspect that Power BI use the key value of the attribute to set the filter and not the name you see and this way the filter will still be October when the month change to November, because the member that used to be called "This Month", but now is call "201710" still have the same key.

I think you will need to create a new attribute for this with a new defined key column and name column, so the value called "This Month" will have the same key value even if the month change.

[Anonymous]

@sdjensen,

Thank you very much! I'm not the owner of the cube so I have to speak with the SSAS team, but I think that this one could be the reason of my problem.

I'm going to tell you if, with your advice, things are improved.

Thank you again.

Best.

[v-chuncz-msft]

@Anonymous,

It seems to work so long as I do not select other value and then save it.

[Anonymous]

@v-chuncz-msft,

Sorry, I don't understand what do you mean. Can you explain me better?

PS:

I didn't touch my reports between 31 of octuber and 2 of november and they are all changed..

[v-chuncz-msft]

@Anonymous,

I can not reproduce the issue.