Doesn't date slicer work with live connection to multidimensional cube

[sdjensen]

Hi,

Doesn't date slicer work with live connections to multidimensional cube? It seem no matter what I do it will only let me select between list or dropdown when I add my date value to the slicer.

I did the following in my cube:

- set dimension type to 'Time'

- attribute type is set to 'Date' (also tried days)

- source of attribute is a datetime column

I hope some one can just tell me how to make this work or that it is not possible, so I don't waste more time 🙂

Thanks in advance.

[vanessafvg · Accepted solution]

name and key dont need to be the same as long as they are the same grain.   Name should be your date, key should be the date key, value is the date.  I usually combine my datekey and date and using the datekey as the key and using the date as the name.

give it a bash, it might work! even if you dont use the key as key, just put date in value

[kaushikd]

Hi @sdjensen

While estb live connections to multidimensional cube, only one thing is there to keep in note:-

the Date Column(let say Start Date) should have date property thats all.

Now when you are connectng to the model and in report view drop a slicer and put Start Date into it.

If still the issue resists please update the Power Bi desktop application

Hope it will help you.

[sdjensen]

I am using may 2017 release of Power BI - there is no newer version available at present time.

I set the following properties to the Date dimension (Dimension Type set to 'Time')

In the Date dimension I have an attribute called 'Date' with the following properties: (Attribute time set to Date (also tried Days) - Source column is called NK_Date and has the datatype Datetime in my source table)

When I add the attribute to a slicer in Power BI I would expect Power BI to recognize this as a date, but I can only see it as a list or dropdown...

This is what I get:

This is what I want:

[vanessafvg]

@sdjensen change the  usage type from regular to calenda

[sdjensen]

Usage can only be set to 'Regular', 'Key' or 'Parent'

Type is set to 'Date' in the 'Date\Calandar' section.

[vanessafvg]

ah i looked to quickly well i am at loss!  sorry feels like ages ago and i am no longer at the same company so can't check the settings.

[sdjensen]

@vanessafvg - well thanks for trying. At least you confirmed that it should be possible. Perhaps I should test this with an older version of PBI Desktop to see if they created this problem in the May release.

[vanessafvg]

i posted one last try, see that before this post,  if that doesn't work  contact microsoft support if no one else responds here

[vanessafvg]

one last guess, in the properties of the date attribute

you should have 3 properties

key

value

name

are all of them populated?  

key = datekey

name = date

value = date

[sdjensen]

With this particular attribute it's only the Key that is populated since name and key should be the same and I never discover a situation where I needed value to create what I want.

[vanessafvg · Accepted solution]

name and key dont need to be the same as long as they are the same grain.   Name should be your date, key should be the date key, value is the date.  I usually combine my datekey and date and using the datekey as the key and using the date as the name.

give it a bash, it might work! even if you dont use the key as key, just put date in value

[sdjensen · Accepted solution]

@vanessafvg - thank you for all you help and suggestions.

The Value property seemed to be the answer. I set it to a nvarchar column with a data format and this made the 'Date.Value' attribute appear in Power BI Desktop.

[Aditya1789]

Hi @sdjensen

I tried the mentioned solution but cannot find Date.Value attribute in Power BI.

Do I need to add it as an Attribute first in my cube.

I have the following attribute as of now:

[sdjensen]

Hi @Aditya1789,

You need to use the Value property on your attribute when designing your calendar dimension in SSDT/BIDS - this should make Date.Value visible when you browse the cube in Power BI Desktop/Power BI Service.

[Aditya1789]

Hi @sdjensen

I fixed it by changing the ordey By property of the Date attirubte to Key.

Thanks,

Aditya

[vanessafvg]

I have had this issue before and i remember now the solution was this

When connecting to SSAS Power BI seems to create a new field called DateField.value underneath the DateField and this is the field one needs to use for using date functionality on certain visuals such as the slicer.

[sdjensen]

That is strange - I don't see this value?

[vanessafvg]

that is very odd, this was in February, so i dont know if there have been any changes since then, where is your surrogate date key, is it the key for your date?

[sdjensen]

My surrogate key is called '_DWID_Date' and it hidden. I always hide my surrogate keys since they don't make sence for the user. In this case it might though - the surrogate key for todays date may 9th 2017 is in my data a int with the value '20170509'

[vanessafvg]

yep just checking, best practice to hide keys, try the solution i just posted

[vanessafvg]

is this from the date dimension in the cube?  i am not sure you can adjust any of the metadata in power bi if you linking to SSAS MDX as as far as i know it expects that to all be set up in the SSAS model.