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

[DM_BI]

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.

[LivioLanzo]

Hi @DM_BI

what is your dax that creates the date table?

[DM_BI]

Hi @LivioLanzo

I used this one : DimDate = CALENDARAUTO()

[LivioLanzo]

Hi @DM_BI

afterwards how are you adding the calculated columns?

[DM_BI]

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.

If you see a solution I would be so grateful.

Thank you for your time,

DM

[DM_BI]

Anyone?

[LivioLanzo · Accepted solution]

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'

[DM_BI]

Hi @LivioLanzo,

Thank you very much it works !!!

Best regards,

DM_BI