Date column value not appearing in axis section

[Anonymous]

Hello,

I'm fairly new to both Power BI and of course, Azure Analysis Services.

I have a question and hope someone out there can answer. When I run side by side comparisons between data sources (raw csv files within BLOB storage, and Azure Analysis Services database, the functionality within Power BI is different between the two. For instance, when I put together a visualization with two columns in my 'Value' and a date column within my 'Axis', within the Power BI visualization that contains local CSV files has more options, like the name of the column within the drop down.

This would give me the unique values per date.

When attempting to do the same within the Power BI visualization that contains the Azure Analysis Services database as my data source, this option is not there as seen in the image below:

and I'm receiving all the data for all values, almost like a missing join, but when comparing the relationships of both the Power BI 'pbix' and model within Analysis Services, the relationship model for both are identical.

I've sinced added a date hierarchy but still no such luck.  Is there an option that needs to be turned on to view the Date column within the drop down, just as in the first example/picture?

Any help would be much appreciated.  

Thank you

[v-sihou-msft]

@Anonymous

In Power BI Service, when we connect live to Analysis Services (no matter Azure or On-premise), we can't do any transform on data field. And it will not automatically generate the date hierarchy for date field. So you'd better create a date hierarchy within SSAS project and deploy.

Also if you have set NameColumn for attribute it will resolve both key column and name column into two fields. The name column is text type. 

Regards,

[Anonymous]

Hello @v-sihou-msft,

I guess I'm a little confused where the date hierarchy should be added within the SSAS model, should it go on the primary or foreign key table?  I'm thinking the primary key table which I've added a date hierarchy named 'calendar' to my date table within SSAS as month/day/year columns as shown below, then deployed the model:

 

After that, I refreshed my Power BI and come up with the following:

The values are still not correct as they should be unique per year, just like in the local csv model.

Thank you again

[Anonymous · Accepted solution]

My issue was with the relationship filter.  It should have been cross filtering, not single.  This solved my problem.