Saturday, 7 March 2020

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.

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

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

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

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.

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

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

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

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

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

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.

SAP HANA Studio, SAP HANA Guides, SAP HANA Learning, SAP HANA Certification, SAP HANA Exam Prep

Thus, the problem is rectified.

No comments:

Post a Comment