Sort by Columns

[Anonymous]

Hello guys,

I created a calculated column which helped me to categorize the tenure of my employees. The results are the following

0-30 days 

31-60 days 

61-90 days

91-180 days

181- 365 days

and so on... you get the idea right?

I create another calculated column "Tenure ID", where i took the category and assigned it an ID 

0-30 days  =1

31-60 days  =2

61-90 days = 3

91-180 days = 4

181- 365 days = 5

and so on...

When i tried to sort the Tenure by Tenure ID power Bi showed me this message

"This column cant be sorted by a column that is already sorted, directly or indirectly, by this column"

How can I sort the Tenure the way Id like to? 

Thanks

[amitchandak]

Have you created both baaed on the same column? Or you calculated buckets first and then on top of bucket you created order by.

In case you have created a bucket and order by on top of it. Then try both on the base column.

Better would be that you create a new dimension based on these values(Kind of hardcoded) and use it.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[jdbuchanan71]

Hello @Anonymous 

When you created your [Tenure ID] column you say you used the [Tenure Category] (your first calculated column) yes?

Can you share the two DAX formulas you used to make the columns?

[Anonymous]

Tenure = IF(
           TODAY() - Agents[Production Date].[Date] <= 30, "0-30 Days", 
             IF(AND(TODAY() - Agents[Production Date].[Date] > 30,TODAY() - Agents[Production Date].[Date] <= 60), "31-60 Days", 
               IF(AND(TODAY() - Agents[Production Date].[Date] > 60, TODAY() - Agents[Production Date].[Date] <= 90), "61-90 Days", 
                 IF(AND(TODAY() - Agents[Production Date].[Date] >90, TODAY() - Agents[Production Date].[Date] <= 180), "91-180 Days", 
                IF(AND(TODAY() - Agents[Production Date].[Date] >180, TODAY() - Agents[Production Date].[Date] <= 365), "181-365 Days", "+365 Days")))))


Tenure ID = SWITCH(Agents[Tenure],
                    "0-30 Days", 1,
                        "31-60 Days", 2,
                          "61-90 Days",3,
                            "91-180 Days", 4,
                                "181-365 Days", 5,
                                    6)

[jdbuchanan71 · Accepted solution]

@Anonymous 

Try changing them to this.

Tenure =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, "0-30 Days",
        _Days <= 60, "31-60 Days",
        _Days <= 90, "61-90 Days",
        _Days <= 180, "91-180 Days",
        _Days <= 365, "181-365 Days",
        "+365 Days"
    )

Tenure ID =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, 1,
        _Days <= 60, 2,
        _Days <= 90, 3,
        _Days <= 180, 4,
        _Days <= 365, 5,
        6
    )

[Anonymous]

@jdbuchanan71  Thanks so much for that, and also to remind me the use of variables to simplify the code haha 

[jdbuchanan71]

I created a sample table and made my category and two sort columns.  On the first sort column I used the category, on the second I replicated the category logic to get the sort.

Because my first sort field looks at the category field I cannot sort the category field using it.  I am basically telling it to sort category by category.

If I sort category by the second sort, the one that does not look at the category field but instead uses the same logic as the category field to determine the sort value it works fine.