cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver III
Resolver III

Why won't my Sort By column work?!

Trying to sort my Fiscal Month & Year by my Fiscal Month and Year KEY, which are sequential numbers formatted as text?

 

Keep getting the error, "Can not sort by column that is either directly or indirectly sorted by this column" or something...

 

datetable.PNG

 

1 ACCEPTED SOLUTION

Hi @CoreyP

 

From your pbix, I found that the column “FiscMon&YrKEY” is created by a IF condition sentence using column “FiscMon&YrKEY”,  so when you sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”, the error happens.

I use columns 'Custom Date'[Fiscal Month] and 'Custom Date'[Fiscal Year] to create “FiscMon&YrKEY” instead, then I can get the same column as before and are able to sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”.

FiscMon&YrKEY = 
IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2017"),"1",
IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2017"),"2",
IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2017"),"3",
IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2017"),"4",
IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2017"),"5",
IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2017"),"6",
IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2017"),"7",
IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2017"),"8",
IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2017"),"9",
IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2017"),"10",
IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2017"),"11",
IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2017"),"12",
IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2018"),"13",
IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2018"),"14",
IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2018"),"15",
IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2018"),"16",
IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2018"),"17",
IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2018"),"18",
IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2018"),"19",
IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2018"),"20",
IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2018"),"21",
IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2018"),"22",
IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2018"),"23",
IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2018"),"24","error"

))))))))))))))))))))))))

Best Regards

Maggie

 

View solution in original post

8 REPLIES 8
Helper II
Helper II

I suspect the index column you are trying to sort this column by does not have a 1-1 unique mapping

 

You might have a January 2017 or a January 2019 that maps to that same numbers

 

usually when you want to the months in chronological order, you should create an index column in the following format, 012019 022019 .... 122019

@ukeasyproj@v-juanli-msft,

 

Thanks for the reply's guys! Let me show you some of my code:

 

Custom Date = CALENDAR(DATE( 2017, 12, 26 ), DATE(2018, 12, 25))
Day of Month = DAY('Custom Date'[Date])
Day of Week = FORMAT('Custom Date'[Date], "DDDD")
Fiscal Month = IF(AND('Custom Date'[Date] >= DATE(2016, 12, 26), 'Custom Date'[Date] <= DATE(2017, 1, 25)), "January", 

IF(AND('Custom Date'[Date] >= DATE(2017, 1, 26), 'Custom Date'[Date] <= DATE(2017, 2, 25)), "February", 

IF(AND('Custom Date'[Date] >= DATE(2017, 2, 26), 'Custom Date'[Date] <= DATE(2017, 3, 25)), "March", 

IF(AND('Custom Date'[Date] >= DATE(2017, 3, 26), 'Custom Date'[Date] <= DATE(2017, 4, 25)), "April", 

IF(AND('Custom Date'[Date] >= DATE(2017, 4, 26), 'Custom Date'[Date] <= DATE(2017, 5, 25)), "May", 

IF(AND('Custom Date'[Date] >= DATE(2017, 5, 26), 'Custom Date'[Date] <= DATE(2017, 6, 25)), "June", 

IF(AND('Custom Date'[Date] >= DATE(2017, 6, 26), 'Custom Date'[Date] <= DATE(2017, 7, 25)), "July", 

IF(AND('Custom Date'[Date] >= DATE(2017, 7, 26), 'Custom Date'[Date] <= DATE(2017, 8, 25)), "August", 

IF(AND('Custom Date'[Date] >= DATE(2017, 8, 26), 'Custom Date'[Date] <= DATE(2017, 9, 25)), "September", 

IF(AND('Custom Date'[Date] >= DATE(2017, 9, 26), 'Custom Date'[Date] <= DATE(2017, 10, 25)), "October", 

IF(AND('Custom Date'[Date] >= DATE(2017, 10, 26), 'Custom Date'[Date] <= DATE(2017, 11, 25)), "November", 

IF(AND('Custom Date'[Date] >= DATE(2017, 11, 26), 'Custom Date'[Date] <= DATE(2017, 12, 25)), "December", 

IF(AND('Custom Date'[Date] >= DATE(2017, 12, 26), 'Custom Date'[Date] <= DATE(2018, 1, 25)), "January", 

IF(AND('Custom Date'[Date] >= DATE(2018, 1, 26), 'Custom Date'[Date] <= DATE(2018, 2, 25)), "February", 

IF(AND('Custom Date'[Date] >= DATE(2018, 2, 26), 'Custom Date'[Date] <= DATE(2018, 3, 25)), "March", 

IF(AND('Custom Date'[Date] >= DATE(2018, 3, 26), 'Custom Date'[Date] <= DATE(2018, 4, 25)), "April", 

IF(AND('Custom Date'[Date] >= DATE(2018, 4, 26), 'Custom Date'[Date] <= DATE(2018, 5, 25)), "May", 

IF(AND('Custom Date'[Date] >= DATE(2018, 5, 26), 'Custom Date'[Date] <= DATE(2018, 6, 25)), "June", 

IF(AND('Custom Date'[Date] >= DATE(2018, 6, 26), 'Custom Date'[Date] <= DATE(2018, 7, 25)), "July", 

IF(AND('Custom Date'[Date] >= DATE(2018, 7, 26), 'Custom Date'[Date] <= DATE(2018, 8, 25)), "August", 

IF(AND('Custom Date'[Date] >= DATE(2018, 8, 26), 'Custom Date'[Date] <= DATE(2018, 9, 25)), "September", 

IF(AND('Custom Date'[Date] >= DATE(2018, 9, 26), 'Custom Date'[Date] <= DATE(2018, 10, 25)), "October", 

IF(AND('Custom Date'[Date] >= DATE(2018, 10, 26), 'Custom Date'[Date] <= DATE(2018, 11, 25)), "November", 

IF(AND('Custom Date'[Date] >= DATE(2018, 11, 26), 'Custom Date'[Date] <= DATE(2018, 12, 25)), "December",
"Errr"))))))))))))))))))))))))
Fiscal Quarter = IF('Custom Date'[Fiscal Month] = "January", "Q1", 
IF('Custom Date'[Fiscal Month] = "February", "Q1", 
IF('Custom Date'[Fiscal Month] = "March", "Q1", 
IF('Custom Date'[Fiscal Month] = "April", "Q2",
IF('Custom Date'[Fiscal Month] = "May", "Q2",
IF('Custom Date'[Fiscal Month] = "June", "Q2",
IF('Custom Date'[Fiscal Month] = "July", "Q3", 
IF('Custom Date'[Fiscal Month] = "August", "Q3", 
IF('Custom Date'[Fiscal Month] = "September", "Q3", 
IF('Custom Date'[Fiscal Month] = "October", "Q4",
IF('Custom Date'[Fiscal Month] = "November", "Q4",
IF('Custom Date'[Fiscal Month] = "December", "Q4", 
"Errr")  )))))))))))
Fiscal Year = IF(AND('Custom Date'[Date] >= DATE(2016, 12, 26), 'Custom Date'[Date] <= DATE(2017, 12, 25)), "2017", "2018")
Fiscal Month & Year = 'Custom Date'[Fiscal Month]&" "&'Custom Date'[Fiscal Year]
Fiscal QuarterYear = 'Custom Date'[Fiscal Year]&'Custom Date'[Fiscal Quarter]
FiscMon&YrKEY = IF('Custom Date'[Fiscal Month & Year] = "January 2017", "1", 

IF('Custom Date'[Fiscal Month & Year] = "February 2017", "2", 

IF('Custom Date'[Fiscal Month & Year] = "March 2017", "3", 

IF('Custom Date'[Fiscal Month & Year] = "April 2017", "4", 

IF('Custom Date'[Fiscal Month & Year] = "May 2017", "5", 

IF('Custom Date'[Fiscal Month & Year] = "June 2017", "6", 

IF('Custom Date'[Fiscal Month & Year] = "July 2017", "7", 

IF('Custom Date'[Fiscal Month & Year] = "August 2017", "8", 

IF('Custom Date'[Fiscal Month & Year] = "September 2017", "9", 

IF('Custom Date'[Fiscal Month & Year] = "October 2017", "10", 

IF('Custom Date'[Fiscal Month & Year] = "November 2017", "11", 

IF('Custom Date'[Fiscal Month & Year] = "December 2017", "12", 

IF('Custom Date'[Fiscal Month & Year] = "January 2018", "13", 

IF('Custom Date'[Fiscal Month & Year] = "February 2018", "14", 

IF('Custom Date'[Fiscal Month & Year] = "March 2018", "15", 

IF('Custom Date'[Fiscal Month & Year] = "April 2018", "16", 

IF('Custom Date'[Fiscal Month & Year] = "May 2018", "17", 

IF('Custom Date'[Fiscal Month & Year] = "June 2018", "18", 

IF('Custom Date'[Fiscal Month & Year] = "July 2018", "19", 

IF('Custom Date'[Fiscal Month & Year] = "August 2018", "20", 

IF('Custom Date'[Fiscal Month & Year] = "September 2018", "21", 

IF('Custom Date'[Fiscal Month & Year] = "October 2018", "22", 

IF('Custom Date'[Fiscal Month & Year] = "November 2018", "23", 

IF('Custom Date'[Fiscal Month & Year] = "December 2018", "24", 
"Errr"))))))))))))))))))))))))
Month & Year = FORMAT('Custom Date'[Date], "MMM")&" "&YEAR('Custom Date'[Date])
Month Name = FORMAT('Custom Date'[Date], "MMMM")
Month Number = MONTH('Custom Date'[Date])
Weekday Number = WEEKDAY('Custom Date'[Date],2)
Week Number = WEEKNUM('Custom Date'[Date],2)
Week Ending = 'Custom Date'[Date] + (7 - 'Custom Date'[Weekday Number])

Also tried making a new KEY column just to see if that would do it, but nope.. same error.

FiscalKEY = 
            IF( 'Custom Date'[Fiscal Month & Year] = "January 2018", "01" ,
                IF( 'Custom Date'[Fiscal Month & Year] = "February 2018", "02" ,
                    IF( 'Custom Date'[Fiscal Month & Year] = "March 2018", "03" ,
                        IF( 'Custom Date'[Fiscal Month & Year] = "April 2018", "04" ,
                            IF( 'Custom Date'[Fiscal Month & Year] = "May 2018", "05" ,
                                IF( 'Custom Date'[Fiscal Month & Year] = "June 2018", "06" ,
                                    IF( 'Custom Date'[Fiscal Month & Year] = "July 2018", "07" ,
                                        IF( 'Custom Date'[Fiscal Month & Year] = "August 2018", "08" ,
                                            IF( 'Custom Date'[Fiscal Month & Year] = "September 2018", "09" ,
                                                IF( 'Custom Date'[Fiscal Month & Year] = "October 2018", "10" ,
                                                    IF( 'Custom Date'[Fiscal Month & Year] = "November 2018", "11" ,
                                                        IF( 'Custom Date'[Fiscal Month & Year] = "December 2018", "12" ,
              "00" ))))))))))))

Here is a link to my pbix file. I removed my data tables for security reasons.

 

 

PBIX file

*bump bump*

 

Was anyone able to help me out of this jam? @v-juanli-msft, I've posted a link to my *.pbix file..

Hi @CoreyP

 

From your pbix, I found that the column “FiscMon&YrKEY” is created by a IF condition sentence using column “FiscMon&YrKEY”,  so when you sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”, the error happens.

I use columns 'Custom Date'[Fiscal Month] and 'Custom Date'[Fiscal Year] to create “FiscMon&YrKEY” instead, then I can get the same column as before and are able to sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”.

FiscMon&YrKEY = 
IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2017"),"1",
IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2017"),"2",
IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2017"),"3",
IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2017"),"4",
IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2017"),"5",
IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2017"),"6",
IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2017"),"7",
IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2017"),"8",
IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2017"),"9",
IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2017"),"10",
IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2017"),"11",
IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2017"),"12",
IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2018"),"13",
IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2018"),"14",
IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2018"),"15",
IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2018"),"16",
IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2018"),"17",
IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2018"),"18",
IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2018"),"19",
IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2018"),"20",
IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2018"),"21",
IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2018"),"22",
IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2018"),"23",
IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2018"),"24","error"

))))))))))))))))))))))))

Best Regards

Maggie

 

View solution in original post

All I needed was a friend to lend a guiding hand
But you turned into a lover and
mother what a lover, you wore me out
All you did was wreck my bed
and in the morning kick me in the head
Oh Maggie I couldn't have tried anymore
You lured me away from home 'cause you didn't want to be alone
You stole my heart I couldn't leave you if I tried

Community Support
Community Support

Hi

I can't reproduce your problem, could you make me clear how you get the column Fiscal Month and Year KEY, is it a column or calculated column? A example pbix file will be really appreciated if you are convenient.

 

Best Regards

Maggie

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors