How sort Switch column values with other order column in power bi

[Gopal_PV]

Hi Friends,

I have imported snowflake view in to Power bi. Now I have created new column using switch for Bucket list of units. Now i want to  order the same bucket list. I want to use same bucket for slicer.
DIY, Small,Medium, Enterprise,DG Enterprise. I Want this same order for slicer.

Units_Bucket =

SWITCH(

    TRUE(),

    -- Non-DG buckets

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] < 25, "DIY",

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 50, "Small",

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 300, "Medium",

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 1000, "Large",

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] > 1000, "Enterprise",

    -- PMI special case

    'Unit'[Parent_Name] = "DG" &&

    'Unit'[PERIOD_UNITS] > 1000, "PMDGIDG Enterprise",

    -- Everything else DG (≤1000) → excluded

    "Excluded"

)

--------------

Units_Bucket_Order =

SWITCH(

    TRUE(),

    'Unit'[Units_Bucket] = "DIY", 1,

    'Unit'[Units_Bucket] = "Small", 2,

    'Unit'[Units_Bucket] = "Medium", 3,

    'Unit'[Units_Bucket] = "Large", 4,

    'Unit'[Units_Bucket] = "Enterprise", 5,

    'Unit'[Units_Bucket] = "DG Enterprise", 6,

    'Unit'[Units_Bucket] = "Excluded", 7

)
Now i am triying to sort Units_bucket  by Bucker Order.
Facing below error. Can you please help me in this.

[v-prasare]

Hi @Gopal_PV,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Shahid12523, @srlabhe & @FBergamaschi  thanks for your prompt response.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are

[Shahid12523]

- Create a lookup table with unique bucket names and order:
- DIY → 1
- Small → 2
- Medium → 3
- Enterprise → 4
- DG Enterprise → 5

- Fix naming in your SWITCH logic so it matches the lookup table exactly.

- Sort the lookup table’s Units_Bucket column by Bucket_Order.

- Use the lookup table’s Units_Bucket in your slicer for correct order and clean filtering.

Let me know if you want a one-table workaround or multilingual slicer support too.

[srlabhe]

Hi Add the belwo as different dataset in Power BI report

Bucket	ID
DIY	1
Small	2
Medium	3
Large	4
Enterprise	5
DG Enterprise	6
Excluded	7

 Then join the Bucket with your main data , 

Goto Data Tab

Click on Newly added dataset 

Click on BUcket Column->Sort based on ID

[FBergamaschi]

Without sample data, I can only suggest ot rewrite the column

Units_Bucket_Order =

SWITCH(

    TRUE(),

    -- Non-DG buckets

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] < 25, 1,

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 50, 2,

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 300, 3,

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] <= 1000, 4,

    'Unit'[Parent_Name] <> "DG" &&

    'Unit'[PERIOD_UNITS] > 1000, 5,

    -- PMI special case

    'Unit'[Parent_Name] = "DG" &&

    'Unit'[PERIOD_UNITS] > 1000, 6,

    -- Everything else DG (≤1000) → excluded

    7

)

And try again the sorting, if it does not work, please upload the pbix in a cloud service and share here the link so I can inspect it and fix

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Gopal_PV]

Hi @FBergamaschi .

Now i am facing cercular depency error. Please find the pbix file in the attachment.

Thank you