Sorting dynamically using Label Columns in Power BI using Hana Calculation Views

[former_member659241]

Introduction:

There may be certain situations where we would need the Axis labels in Charts to be sorted Alphabetically or sort as per the requirement of Customer. This is not possible to be done directly over the Row labels. So, in this blog post I have discussed the workaround for this problem.

Main Problem:

While developing an Inventory Ageing Report I had to sort the Labels according to the Ageing Buckets in the order as mentioned by my Customer. It is not possible to sort the labels in Power BI dynamically. So, we were thinking of a workaround for this problem.

When I displayed the view in Analysis tab in HANA Studio, I was able to find that the Ageing Bucket Labels were not sorted in the required order.



The order that I need to sort my labels is shown in the below screenshot.



Solution:

So, in order to sort the labels in the order that I need, I had to search for a workaround. So, I tried to achieve this in the Semantics Layer of Calculation view and bring the labels sorted in the required order into Power BI. For this, kindly follow the steps mentioned below:

1.Create a Calculated Column to number the buckets in the required order. Here, I have created a Calculated Column in the name ‘Ageing_key’ and have used 'if' condition to assign the order to the respective Ageing bucket.



2.In Semantics layer assign AGEING_BUCKET as the Label Column for Ageing_Key



3.As shown in the below screenshot, the Ageing Bucket is sorted in the Order of Ageing Key.



4.Now, open Power BI and select Ageing_Key field in place of Ageing_Bucket. You can see that the Label Names appear in the sorted order.



Thus, the problem is rectified.

Conclusion:

So, from this blog post you could have got an idea on how to sort the Row Label names in the Required order. Please feel free to share your views and ideas on the same and also share if you have any workarounds for this problem.

Reference:

• ‘How to sort dimension values in SAP Analytics Cloud (SAC) filter dynamically in order of measure from HANA’ from the link provided below: https://apps.support.sap.com/sap/support/knowledge/public/en/2830016