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

Error when sorting by column (and table made with DAX language)

Hello,

 

I created a DimDate (with a DAX formula calendar auto) and added natural months. Then I added a column "month_inter" because the start and end of my "month_inter" are very different from natural months. Then I wanted to sort them in the right order (not alphabetically) so I created a column "num_month".

 

But when I click on "sort by column" I have this error message "You cannot sort this column by a column that is already sorted, directly or indirectly, by this column." 

 

And I cannot add my "num_month" column in Power Querry because my table DimDate has been made with DAX so it doesn't appear in Power Querry.

 

Anyone know how can I sort my months by the order of "num_month" ?

 

Thank you very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Error when sorting by column (and table made with DAX language)

Hi @DM_BI,

 

you cannot sort mes inter by num because the value of num depends on mes inter. 

Try to calculate your 'num' column the same way you calculate the 'mes inter' column but by assigning the month number instead of the month name. And then you can calculate the 'mes inter' based on the 'num' column. afterwards you should be able to sort 'mes inter' by 'num'

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

7 REPLIES 7
Highlighted
Solution Sage
Solution Sage

Re: Error when sorting by column (and table made with DAX language)

Hi @DM_BI

 

what is your dax that creates the date table?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Helper III
Helper III

Re: Error when sorting by column (and table made with DAX language)

Hi @LivioLanzo

 

I used this one : DimDate = CALENDARAUTO()

Highlighted
Solution Sage
Solution Sage

Re: Error when sorting by column (and table made with DAX language)

Hi @DM_BI

 

afterwards how are you adding the calculated columns?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Helper III
Helper III

Re: Error when sorting by column (and table made with DAX language)

I added the following columns :

 

Día = DAY(DimDate[Date].[Date])

 

Num_Mes = MONTH(DimDate[Date].[Date])
 
Mes = FORMAT(DimDate[Date];"MMM")
 
Semana = 'DimDate'[Date] - WEEKDAY(DimDate[Date];2) +1
 
Año = YEAR(DimDate[Date].[Date])
 
Año academico = if(DimDate[Date]>=DATE(2018;11;05)&&DimDate[Date]<=DATE(2019;11;01);"2018/2019";IF(DimDate[Date]>=DATE(2017;11;06)&&DimDate[Date]<=DATE(2018;11;04);"2017/2018";IF(DimDate[Date]<=DATE(2017;11;05);"Antes")))
 
Mes Inter = if(DimDate[Date]>=DATE(2017;11;06)&&DimDate[Date]<=DATE(2017;12;01);"Nov";if(DimDate[Date]>=DATE(2017;12;02)&&DimDate[Date]<=DATE(2018;01;07);"Dic";if(DimDate[Date]>=DATE(2018;01;08)&&DimDate[Date]<=DATE(2018;02;04);"Ene";if(DimDate[Date]>=DATE(2018;02;05)&&DimDate[Date]<=DATE(2018;03;04);"Feb";if(DimDate[Date]>=DATE(2018;03;05)&&DimDate[Date]<=DATE(2018;04;01);"Mar";if(DimDate[Date]>=DATE(2018;04;02)&&DimDate[Date]<=DATE(2018;05;06);"Abr";if(DimDate[Date]>=DATE(2018;05;07)&&DimDate[Date]<=DATE(2018;06;03);"May";if(DimDate[Date]>=DATE(2018;06;04)&&DimDate[Date]<=DATE(2018;07;08);"Jun";if(DimDate[Date]>=DATE(2018;07;09)&&DimDate[Date]<=DATE(2018;08;05);"Jul";if(DimDate[Date]>=DATE(2018;08;06)&&DimDate[Date]<=DATE(2018;09;02);"Ago";if(DimDate[Date]>=DATE(2018;09;03)&&DimDate[Date]<=DATE(2018;10;07);"Sep";if(DimDate[Date]>=DATE(2018;10;08)&&DimDate[Date]<=DATE(2018;11;04);"Oct";if(DimDate[Date]>=DATE(2018;11;05)&&DimDate[Date]<=DATE(2018;12;02);"Nov";if(DimDate[Date]>=DATE(2018;12;03)&&DimDate[Date]<=DATE(2018;12;30);"Dic";if(DimDate[Date]>=DATE(2018;12;31)&&DimDate[Date]<=DATE(2019;02;03);"Ene";if(DimDate[Date]>=DATE(2019;02;04)&&DimDate[Date]<=DATE(2019;03;03);"Feb";if(DimDate[Date]>=DATE(2019;03;04)&&DimDate[Date]<=DATE(2019;03;31);"Mar";if(DimDate[Date]>=DATE(2019;04;01)&&DimDate[Date]<=DATE(2019;05;05);"Abr";if(DimDate[Date]>=DATE(2019;05;06)&&DimDate[Date]<=DATE(2019;06;02);"May";if(DimDate[Date]>=DATE(2019;06;03)&&DimDate[Date]<=DATE(2019;06;30);"Jun";if(DimDate[Date]>=DATE(2019;07;01)&&DimDate[Date]<=DATE(2019;08;04);"Jul";if(DimDate[Date]>=DATE(2019;08;05)&&DimDate[Date]<=DATE(2019;09;01);"Ago";if(DimDate[Date]>=DATE(2019;09;02)&&DimDate[Date]<=DATE(2019;10;06);"Sep";if(DimDate[Date]>=DATE(2019;10;07)&&DimDate[Date]<=DATE(2019;11;03);"Oct";BLANK()))))))))))))))))))))))))
 
And now my following steps are :
- sorting Mes inter by the order I want (the year starts on November and ends in October because I work in education sector, Nov would be Month 1, December = 2, etc.)
- create a column current month that put 1 if we are in the current Mes Inter and 0 if not
- crete a column current quarter that put 1 if we are in the current Quarter that takes into account the start and end of my Mes Inter, and 0 if not.
 
Captura dimdate.PNG
 
 
If you see a solution I would be so grateful.
 
Thank you for your time,
 
DM
Highlighted
Helper III
Helper III

Re: Error when sorting by column (and table made with DAX language)

Anyone?

Highlighted
Solution Sage
Solution Sage

Re: Error when sorting by column (and table made with DAX language)

Hi @DM_BI,

 

you cannot sort mes inter by num because the value of num depends on mes inter. 

Try to calculate your 'num' column the same way you calculate the 'mes inter' column but by assigning the month number instead of the month name. And then you can calculate the 'mes inter' based on the 'num' column. afterwards you should be able to sort 'mes inter' by 'num'

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Highlighted
Helper III
Helper III

Re: Error when sorting by column (and table made with DAX language)

Hi @LivioLanzo,

 

Thank you very much it works !!!

 

Best regards,

 

DM_BI

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors