Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CupidC
Helper II
Helper II

Trouble Sorting Month Names with Custom Column in Data View


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..

 

CupidC_0-1708407795978.png

 

1 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.

vcgaomsft_0-1708580576165.png

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

View solution in original post

3 REPLIES 3
Ritaf1983
Super User
Super User

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 :
Ritaf1983_0-1708408881005.png

pbix is attached

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

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...

 

CupidC_0-1708409863279.png

 

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.

vcgaomsft_0-1708580576165.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors