Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I am trying to create a filter based on date where the year, quarter and day are nested. I would like the filter to be sorted to always show descending dates (2024, 2023, 2023; followed by Q4,Q3,Q2,Q1, followed by 30 July, 29 July, 28 July, etc.).
It only seems possible to apply a descending sort on one of the fields, but the other fields will automatically be sorted ascending. Is there a way around it?
@Sandra_aaA , You can create a new Custom sort column
Proud to be a Super User! |
|
Thanks for the answer @bhanu_gautam.
I am working with an ingested semantic model, so I am not able to add calculated columns. Is there an alternative solution for this? Something using a measure perhaps? (I doubt it, but doesn't hurt to ask!)
Try using this measure
SortYear = MAX('Table'[Year])
SortQuarter =
SWITCH(
MAX('Table'[Quarter]),
"Q1", 4,
"Q2", 3,
"Q3", 2,
"Q4", 1
)
SortDay = 100 - MAX('Table'[Day])
SortOrder =
[SortYear] * 100000 +
[SortQuarter] * 1000 +
[SortDay]
Proud to be a Super User! |
|
Thanks, but I have two issues with this solution:
1. The sorting order isn't actually correct. Is to be expected, because it still takes the Days in ascending order:
2. How do I use the measure to sort in a filter?
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |