Issuer sorting column created by union

[stevedata]

I created a table using union and created a sort order

TotalRegionsorder = UNION(DISTINCT(Main[Region]),{"Global"})

RegionGlobal	Sortorder
US	1
ZA	2
Global	3

In a matrix table first row is RegionGlobal and i want to sort using my sort order but it doesnt work,

It also doesnt work when i have RegionGlobal as a legend in a clusterd column chart and i try to sort by RegionGlobal

In my matrix chart when i replace RegionGlobal with Sortorder, only the first 2 rows apper in the table and Global dissappers

When i try and sort the RegionGlobal column by the sortider column i get a circular dfependancy error

A circular depoendancy was detected: Totalregionsorder[RegionGlobal], Totalregionsorder[Regionorder],Totalregionsorder[RegionGlobal].

Can anyone help? 

[v-aatheeque · Accepted solution]

Hi @stevedata ,

Based on the above error , need to remove [RegionMEASURE0]) measure from the calculated column , because its wont supported.
Use this DAX : 

RegionOrder2 =
SWITCH(
    TRUE(),
    TotalRegionsorder[RegionGlobal] = "Global", 1,
    TotalRegionsorder[RegionGlobal] = "US", 2,
    TotalRegionsorder[RegionGlobal] = "ZA", 3,
    999  -- default fallback numeric value
)

expected output :

Hope this helps !

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thanks for using Microsoft Fabric Community Forum.

[Kishore_KVN]

Hello @stevedata, is it possible for you to share any sample pbix?

Thanks,

Kishore

[v-aatheeque]

Hi @stevedata ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @stevedata ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[v-aatheeque]

Hi @stevedata ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[stevedata]

Still no solution found im afraid.

I tried create a SWITCH column to assign order below - but when I try and sort the RegionGlobal column I still get the circular dependancy issue.

RegionOrder2 = SWITCH(
TRUE(),
TotalRegionsorder[RegionGlobal] = "Global", 1,
TotalRegionsorder[RegionGlobal] = "US",2,
TotalRegionsorder[RegionGlobal] = "ZA",3,
[RegionMEASURE0])

error i then get: 

A circular dependancy was detected: Totalregionsorder[RegionGlobal], Totalregionsorder[Regionorder2],Totalregionsorder[RegionGlobal].

[v-aatheeque · Accepted solution]

Hi @stevedata ,

Based on the above error , need to remove [RegionMEASURE0]) measure from the calculated column , because its wont supported.
Use this DAX : 

RegionOrder2 =
SWITCH(
    TRUE(),
    TotalRegionsorder[RegionGlobal] = "Global", 1,
    TotalRegionsorder[RegionGlobal] = "US", 2,
    TotalRegionsorder[RegionGlobal] = "ZA", 3,
    999  -- default fallback numeric value
)

expected output :

Hope this helps !

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thanks for using Microsoft Fabric Community Forum.

[v-aatheeque · Accepted solution]

Hi @stevedata ,

Have you resolved the issue? If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!