Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
)
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
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?
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
)
@jdbuchanan71 Thanks so much for that, and also to remind me the use of variables to simplify the code haha
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |