- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sort by Quarter not Working with an Index
I am trying to sort data in a stacked graph using a Date Table. I have a column with Dates called Date and a number of data attribute so I can use them in the visuals.
I am trying to get an aggregate set of values by the quarter and their year. What I'm getting is the following error:
My Data is arranged like this:
How can I get this to sort correctly on a visual? Notice the following:
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is the code that creates the table in your model.
Index Date = CALENDAR (DATE(2019,1,1), DATE(2020,06,30))
Then you added the calculated columns. I just changed the code to include the additional fields in the original DAX that creates the table.
So you can
- Delete all the columns except the date column from your table.
- Replace the measure that creates the table with mine.
This
Index Date = CALENDAR (DATE(2019,1,1), DATE(2020,06,30))
becomes this
Index Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 06, 30 ) ),
"Quarter", QUARTER ( [Date] ),
"Month", MONTH ( [Date] ),
"Quarter and Year", "Q" & QUARTER ( [Date] ) & YEAR ( [Date] ),
"DateKey", FORMAT ( [Date], "yyyymmdd" ),
"Year", YEAR ( [Date] ),
"Year Quarter", YEAR ( [Date] ) & " Q" & QUARTER ( [Date] ),
"QYSort", YEAR ( [Date] ) * 100 + QUARTER ( [Date] )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous
Add a column to your date table like so.
QYSort = YEAR ( [Date] ) * 100 + QUARTER ( [Date] )
Then you can set the 'Sort By' column on 'Quarter and Year' to be the new 'QSort'
The error is because, for every instance of Q42019 it need 1 and ONLY 1 numeric value in the column you are trying to use as the sort. Your index column would have 90 different numbers under Q42019. The QYSort column will give you the single numeric value you need.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your help @jdbuchanan71 . That still didn't work.
I'm learning power BI.
1) I get a circular reference for sorting so it appears there is some persistence on the column sort. I can't seem to delete the sorting except doing a sort ascending on the column?
2) I have these indexes already. See below. The date column is sorted Ascending. the 'Quarter and Year' field should have a reference already to it.
Did I miss something in your answer?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is a sample of the default sort in [Quarter and Year]
On the date table I have to:
- Select the column I want to sort.
- Click the 'Sort by column' drop down
- Pick the filed I want to sort by
Then the model will sort my filed in the correct order.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @jdbuchanan71 . So this appears to be something on the sorting being persisted on the table itself. When you do it appears to work. For me this is the error I get:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, that means that for some reason it is seeing something like this.
Quarter and Year | QYSort |
Q1 2019 | 201901 |
Q1 2019 | 201902 |
Can you save a copy of your .pbix with just the date table and share it here? Load it to onedrive or drop box and post the link here.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Part of the problem is in your [Quarter and Year] column. If you look at the screen shot the dates cross 2 quarters but they are all tagged Q12019: The problems is you are taking the QUARTER ( ) of the [Quarter] column.
But even with that fixed I was getting circular errors when I should not have been. Try building the table all in one shot rather than adding the columns one at a time.
Index Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 06, 30 ) ),
"Quarter", QUARTER ( [Date] ),
"Month", MONTH ( [Date] ),
"Quarter and Year", "Q" & QUARTER ( [Date] ) & YEAR ( [Date] ),
"DateKey", FORMAT ( [Date], "yyyymmdd" ),
"Year", YEAR ( [Date] ),
"Year Quarter", YEAR ( [Date] ) & " Q" & QUARTER ( [Date] ),
"QYSort", YEAR ( [Date] ) * 100 + QUARTER ( [Date] )
)
Then you can add the [Index Column] as a new calculated column although I don't think you need that one. I was able to get the sorting to work fine in a new book.
I was able to get it to work by deleting all the additional columns you had added then replacing your first measure with the code above. Then the sorting worked fine.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Interesting. Where is the code to create the table? Could I have edited that? Is this in query editor?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jdbuchanan71 This is the solution. Thanks for this. Now I know to debug the table itself because I think PBI gets confused.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is the code that creates the table in your model.
Index Date = CALENDAR (DATE(2019,1,1), DATE(2020,06,30))
Then you added the calculated columns. I just changed the code to include the additional fields in the original DAX that creates the table.
So you can
- Delete all the columns except the date column from your table.
- Replace the measure that creates the table with mine.
This
Index Date = CALENDAR (DATE(2019,1,1), DATE(2020,06,30))
becomes this
Index Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 06, 30 ) ),
"Quarter", QUARTER ( [Date] ),
"Month", MONTH ( [Date] ),
"Quarter and Year", "Q" & QUARTER ( [Date] ) & YEAR ( [Date] ),
"DateKey", FORMAT ( [Date], "yyyymmdd" ),
"Year", YEAR ( [Date] ),
"Year Quarter", YEAR ( [Date] ) & " Q" & QUARTER ( [Date] ),
"QYSort", YEAR ( [Date] ) * 100 + QUARTER ( [Date] )
)
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
06-06-2024 01:19 AM | |||
10-08-2024 09:15 PM | |||
08-10-2018 10:49 AM | |||
12-15-2024 10:24 PM | |||
05-01-2024 08:34 PM |
User | Count |
---|---|
102 | |
74 | |
42 | |
39 | |
30 |
User | Count |
---|---|
161 | |
87 | |
64 | |
46 | |
42 |