Forum Discussion
Sort by Columns
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
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 )
6 Replies
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?
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.
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)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 )
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
