SSAS Dimension Sorting While Browsing In Excel

Question

I have a SSAS cube which I browse in Excel, The problem is order of dimension. I Have a dimension named accounts which has hierarchy to show list of accounts, but excel shows accounts in alphabetical order based on there name, while I want them to be displayed based on the key field that is AccountID.

Further If I share this cube in Power BI as a dataset, and let users connect this dataset through Excel, can control the order of sorting while browsing the same in excel.

Answer 1

Are you talking about the order of attributes in the dimension?

AccountID AccountName
1         Credit
2         Automobile
3         Lease

Your current order is Automobile, Credit, Lease but you want it as depicted in the table?

If so you need to set the "order by" attribute in the individual attributes to "Key" instead of "Name", with Key being AccountID and Name being AccountName. (In SSDT: Rightclick the dimension attribute -> Properties, the 10th entry or so)