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.

[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