How to Custom Sort Text Column

In data visualization sorting is very useful to see the organized data in meaningful order as we required. Sorting the numbers in ascending or descending is quite simple in PBI. Similarly sorting text field is also simple in alphabetical ascending or descending order. But what if the client wants the text order as customized without having the sorting column in the table?

lets look the data and understand the requirement first,

I have two tables Exclusions & Inclusions. The Exclusion data will updated daily basis,
It could either add or remove the text fields in the table.
Now I want to see the today’s excluded text fields in first and following remaining text fields in the slicer as well as table.

Here is the sample data

based on the above data, I want the orders like the below and it should change dynamically when updating the data in the exclusions table.
Chennai
Madurai
Salem
Theni
Thenkasi
Coimbatore
Dinduigal
Ramanathapuram
Tirunelveli
Trichy
Virudhunagar

Lets using power query to achieve this,

Create Duplicate Exclusion table
Filter the data as Max Date available in the table,
add this M syntax in Advanced Editor steps

MaxDate = List.Max(Exclusions[Date]),
FilteredRows = Table.SelectRows(Exclusions, each [Date] = MaxDate)

and remove other columns

Now Duplicate the Inclusion table and follow the steps attached the images below
Removed Duplicates
Sorted Rows
Merged Queries
Expanded Exclusions Sort
Merged Columns

Removed Duplicates

Sorted Rows

Merged Queries

Expanded Exclusions Sort

Merged Columns

Now Create Final Sort Table by using Append both the tables we created,

Append Queries as New
Merge Column
Create Index Column

Append Queries as New

Merge Column

Create Index Column

Final Output

Here is the Data Modeling view

Yeah! we can get the Output as requested by using Power Query Steps.

Now lets try the above in DAX,

Lets create the Exclusion Sort & Inclusions Sort table and by using these tables create Final Sort table in simple DAX query

Final Sort DAX =

VAR MaxDate = CALCULATE(MAX(Exclusions[Date]))
VAR A = CALCULATETABLE(VALUES(Exclusions[City]), Exclusions[Date] = MaxDate)
VAR B = CALCULATETABLE(VALUES(Inclusions[City]))
VAR C = EXCEPT(B,A)
RETURN
UNION(A,C)

The above simple dax created the Final Sort table, But the problem is if you use the created table in slicer or Table it automatically sorts as alphabetical order,

So we should have to create the Sorting column as the order requested, I will leave it to you!

Try to create the Sorting Order column in Calculated Table DAX or Calculated column in the Final Sort table, Comment if you created the sorting column.

Appreciated & Thanks in advance. See you in the next use case..