Trouble Sorting Month Names with Custom Column in Data View

[CupidC]

Hello, I'm encountering a problem while attempting to create a sorting column using the SWITCH function. After creating the column, I am unable to sort the month names using this newly created sort column due to a dependency error message. Could anyone advise on the optimal solution to this issue within the Data View (not in the Power Query view)?

Thanks in advance..

[Ritaf1983]

Hi @CupidC 
You can parse the month directly from the "Date " column with
Month_num = month ('your table[date]) 
Or just create a date table with all needed columns with a script like :

Dates 4 =

  GENERATE (

    CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()),

    VAR currentDay = [Date]

    VAR day = DAY( currentDay )

    VAR month = MONTH ( currentDay )

    VAR month_name =

    SWITCH(MONTH(currentDay),

    1,"Jan",

    2,"Feb",

    3,"Mar",

    4,"Apr",

    5,"May",

    6,"Jun",

    7,"Jul",

    8,"Aug",

    9,"Sep",

    10,"Oct",

    11,"Nov",

    12,"Dec")

    VAR year = YEAR ( currentDay )

  RETURN ROW (

    "day", day,

    "month", month,

    "year", year ,

    "month name" ,month_name)

 )

and it will work :

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[CupidC]

Hi Ritaf1983

Thanks. What if the column isn't related to months, as in the example below? Are there any methods to create a sorting column in such cases? thanks...

[v-cgao-msft · Accepted solution]

Hi @CupidC ,

If there are not many categories(fruit field) and they will not continue to grow, consider creating the input table as a Dim table (and sort) and then connect to the fact table.

Best practice continues to be to take this calculation downwards: dax --> power query m --> data source.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group