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! I was hoping to get some thoughts on if I'm missing something when sorting a table column visual. Here is the current sorting structure of the table:
As well as the DAX logic for the Review Priority column:
Review Priority =
SWITCH(
TRUE(),
'Main data'[Inherent Risk Score] = "5" && 'Main data'[Review Notification] = "Out of compliance",
"#1 Priority",
'Main data'[Inherent Risk Score] = "4" && 'Main data'[Review Notification] = "Out of compliance",
"#2 Priority",
'Main data'[Inherent Risk Score] = "3" && 'Main data'[Review Notification] = "Out of compliance",
"#3 Priority",
'Main data'[Inherent Risk Score] = "5" && 'Main data'[Review Days] >=0 && 'Main data'[Review Days] <=30,
"#4 Priority",
'Main data'[Inherent Risk Score] = "4" && 'Main data'[Review Days] >=0 && 'Main data'[Review Days] <=30,
"#5 Priority",
'Main data'[Inherent Risk Score] = "3" && 'Main data'[Review Days] >=0 && 'Main data'[Review Days] <=30,
"#5 Priority",
--'Main data'[Inherent Risk Score] = "5" &&
'Main data'[Review Days] >=31 && 'Main data'[Review Days] <=730,
"#6 Priority",
// 'Main data'[Inherent Risk Score] = "4" && 'Main data'[Review Days] >=31 && 'Main data'[Review Days] <=730,
// "#7 Priority",
// 'Main data'[Inherent Risk Score] = "3" && 'Main data'[Review Days] >=31 && 'Main data'[Review Days] <=730,
// "#8 Priority",
FORMAT('Main data'[Review Days], "####")
)
The Review Days are a simple DATEDIFF from the Review Date to TODAY() The Review Notification is just a bucketing of certain Review Days ranges and IR Score is just a whole number.
What I'm trying to do is sort the column by the correct Review Priority nominclature. In the above screenshot, the sorting is working correctly. I am taking the highest IR Score into account along with the lowest Review Day to obtain a priority list. This is correctly functioning for both red and yellow colors.
Here is where the sorting is off:
In this case I am trying to not take the IR score into account anymore, as seen in the DAX code. When I leave out the IR Score component, the sorting on Days just becomes illogical. Further more, if I change the code to ignore any sort of Priority and just display the Days:
I am forced to change the Days value into a text (as expected), but this leads to a text-type sorting, which is definitely not what I want.
So, to summarize- I'm trying to sort on Priority 1-3 at the top, but don't really care about sorting anything beyond that other than having it respect the numerical Days sorting. Is there a way to accomplish this or is this just what I have to deal with when sorting by text?
I've love to hear peoples thoughts, hopefully I missed some sorting logic somewhere! Thank you!
Solved! Go to Solution.
Just in case anyone has a similar issue, I solved this problem by creating a new table.
Just in case anyone has a similar issue, I solved this problem by creating a new table.
Apologies, but I'm bumping this thread, I'd love either a solution to the sorting issue, or a confirmation and possibly explaination that this is working as intended. Many thanks!
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |