Day 19 - Custom Sorting in Power BI

I have taken a simple dataset to explore the sorting feature in POWER BI.

Let's plot a Matrix chart using the dataset.

Observation 1: 

By default , Alphabetical order Sorting has been done on  attribute - 'Student Name'

Observation 2: 

We have option to sort by either 'Student Name' or 'Percentage'. That's too in ascending or descending order.

Problem Statement : We have to display the data in same sequence as it's in dataset.

In this case, we need our own customized sort order that is neither based on alphabets or values.


Index column is very handy in such situations to create a a custom sort order. 

Step 1: Navigate to Transform Data > Transform Data > Add Column > From 1 > Close & Apply 

Step 2:  Navigate to 'Data View' > Attribute 'Student Name' > Column Tools > Sort by Column > Select Index

Yeah, we incorporated the requested change successfully !!!


We might have to create a custom index column in some of the scenarios to meet the client's requirement.

Please follow step-by-step instruction to create a custom-index column.

Step 1: Create a New Column - CustomIndex where Index value is controlled by attribute -'Student Name'

You should know these DAX functions !!!
<< 

Returns a table that represents a subset of another table or expression.
FILTER(<table>,<filter>)

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )


One way to obtain a rank value for a given value in a row is to count the number of rows, in the same table, that have a value larger (or smaller) than the one that is being compared. This technique returns a blank or zero value for the highest value in the table, whereas equal values will have the same rank value and next value (after the equal values) will have a non consecutive rank value.
EARLIER(<column>, <number>)

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

COUNTROWS(<table>)

>>

CustomIndex =

VAR __table = FILTER(ALL(Student),Student[Student Name]> EARLIER(Student[Student Name]))

RETURN

COUNTROWS(__table) + 1

In the below example, there is only one name which is having larger value than 'Sardar'.
So output of COUNTROWS(__table) + 1 = 1 + 1. That's the reason CustomIndex value for Student - Sardar is 2.
Hope this explanation helps you in understanding the logic.

Step 2: Create a New Column - CustomComboIndex where Index value is controlled by a pair of attributes -'Student Name' & 'Percentage'

CustomComboIndex =

VAR __table = FILTER(ALL(Student),Student[Student Name] > EARLIER(Student[Student Name]))

VAR __tableSame = FILTER(ALL(Student),Student[Student Name]= EARLIER('Student'[Student Name]) && 'Student'[Percentage] > EARLIER('Student'[Percentage]))

RETURN

COUNTROWS(__table) + 1 + COUNTROWS(__tableSame)

#RR #Day19 #PowerBI #CustomSorting #DAXFunctions #FILTER #ALL #EARLIER #COUNTROWS #HappyLearning #WeLearnEveryday