Skip to Content
Technical Articles
Author's profile photo Bhavya Sree M K1708

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

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

 

 

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo ARPITH BHAT
      ARPITH BHAT

      Hello!!

      It is an interesting blog. Long way to go Bhavya!!

      Author's profile photo Eronita Scott
      Eronita Scott

      This is a nice post and gives lots of information to us

      Calculation Views are used to consume other Analytic, Attribute and other Calculation views and base column tables. These are used to perform complex calculations, which are not possible with other type of Views.

      Author's profile photo Bhavya Sree M K1708
      Bhavya Sree M K1708
      Blog Post Author

      Thanks

      Author's profile photo Prasath Elumalai
      Prasath Elumalai

      Hi Sree

      Nice blog post Keep continue.:) 

       

      PS :Name edited

       

      Prasath

      Author's profile photo HANISH SHAN S
      HANISH SHAN S

      Blog is so insightful Bhavya

      Regards,

      Hanish Shan S