Date slicer filter doesn't work with dates from SSAS Cube?

[cubadrum]

Hello,
I worked on a report with Excel as a data source, my time filters became a cool Date Slicers filters. (I guess because it is modeled with Date format).
But when I work with the perspective of an SSAS cube, my date fields no longer behave the same, it stays like a simple list filter.

Is this enhancement of date slicers compatible with data from an SSAS cube?

Regards.

[gusmundo]

Accepted answer is for Tabular SSAS models as multidimensional SSAS models do not make use of Power BI date filtering.

[v-qiuyu-msft]

Hi @cubadrum,

It seems that you connect to the SSAS cube in Import mode, and place the date field in a slicer. The slicer only provide List and Dropdown modes, right?

Based on my test, the issue is caused by that date field is converted to TEXT data type automatically when connecting to SSAS cube in import mode. We need to change it to Date data type then Between, Before, After modes will be available in the slicer. 

Best Regards,
Qiuyun Yu

[cubadrum]

Hello @v-qiuyu-msft,

Thanks for the reply!!
I forgot to comment that I use the "Connect Live" option to connect to the cube. Using this option I see that the options to modify the data type are disabled.
In the cube, the field is set as a date, but in Power BI desktop, it does not take this configuration.

Regards.

[v-qiuyu-msft · Accepted solution]

Hi @cubadrum,

When we retrieve data from the keyColumns, it use the NameColumn data type actually. In your scenario, I would suggest you set the data type for the ValueColumn as Date type. Then place Date.value field in the slicer visual.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-MSFT,

I have changed the basic setting Type attribute to Calendar->Date.

Also, i have set key column to Int, Name column to WChar and Value column to Date datatypes.

I am still unable to see any additional measure 'with an extension ".Value" in power Bi desktop.

The things that i have tried so far are:

1. Changing Name fields format to yyyy-MM-dd.

2. Including Value field as date. 

3. Changing Value field's format to yyyy-MM-dd.

4. Removing Name column and just retaining key and value columns.

None of these seem to work on my SSAS multidimensional cube.

 Regards,

Chetan A.

[Anonymous]

Does anybody have a solution to this? 

I cant see the .value columns, is this some hidden setting when doing a live connection to SSAS?

Any other workarounds?

Regards

Jon

[Anonymous]

So it turns out it wont display the value property if its part of a hierarchy.

Once I turned on hieararchy to visible for the date key, it popped up with the value.

Mystery solved.

[Anonymous]

This worked for me! Thanks!

[cubadrum]

Awesome @v-qiuyu-msft
This resolved my problem. Thanks.