Default member issue

[AbdulMukhtar]

I am trying to create Power BI desktop report from SSAS database using Live connection method.

current month is defined as default member for date dimension in the cube.

when i drag year attribute to the filter or report axis area it is showing only current year even though cube contains previous years data.

any one face this issue?

[Ethernet_Jack · Accepted solution]

I had the same issue and it was making me bonkers. Our cube had a default set for date for yesterday. The key was that the default was set at the lowest level of our date heirarchy. So I added a report filter at the bottom and used advanced filter on the lowest level of our heirarchy to say "DATE contains '20'.  That will pull everything this century, which covers the whole cube.  Then I could use other elements of the heirarchy to slice into quarters/years/ whatever.  I think this is still a silly roadblock that PowerBI needs to sort out, (and putting dates in date order instead of integer order), but at least the work around was easy.

Hope that helps!

[v-micsh-msft]

Hi AbdulMukhtar,

I think this should be caused by the date hierarchy used as dimensions in your cube.

The explanation from TechNet:

“The default member of an attribute hierarchy is used to evaluate expressions when an attribute hierarchy is not included in a query. The default member is ignored whenever a query includes an attribute hierarchy or user hierarchy that contains the attribute that sources the attribute hierarchy. This is because the member specified in the query is used.”

Just from the result, this should means the year attribute and the month attribute that configured with default member are not under the same hierarchy, so month attribute is not included in the current query, which makes it to generate the current month.

Check the blog for more information:

"Gotchas" With Using an SSAS Default Member

Please post back if you need any further assistance.

Regards

[AbdulMukhtar]

Hi Micheal_Shao,

Thanks for the reply.

How can i get it to work in power bi?

I am trying following things:

1) Year wise sales graph (Currently : It is showing only current year)

2) When user clicks on year, it should drill down to Quarter wise sales and when we click on Quarter it should show monthwise sales.(Currently : When we click on current year it is showing current quarter and when we click on quarter it is showing current month. We think it is because of default member which we have set for month attribute to current month.

Thanks

[v-micsh-msft]

Hi AbdulMukhtar,

Well, I am not sure what the data hierarchy looks like, just from my view, I would try to remove the default member settings.

Another approach that should work is to set the month in the Year-Quator-month hierarchy to be the default member (so if year is selected this time, it will query the year data and ingore the default member configuration), which need to alter the data hierarchy at the data source side.

Regards