Sort by Month in Year when using Direct Query Mode

Data Visualizations
1

In my visual, the X axis is Month Short Name but I need the graph to order by Month of Year. This is easily accomplished using Sort by Column when using imported data, but it’s not available in direct query mode. Rather, I have set my query for the Date table to sort by Month in Year and the date data sorts correctly in Power Query Editor. Unfortunately, that sort does not affect my visual. How can I sort by Month of Year but display Month Short Name in the graph? Thanks.

2

You can have a “Month Number” column and “short month” name in "Date "Table or your other fact table . Once you have Month Numbered 1 to 12, You can select Short month column and Sort it by Month Number column. Below is the screenshot of the how to do:

Date Table
Date Table1022×598 58.1 KB

3

Hi @pshetty,

Just curious about options. In my Power BI desktop, I don’t get these options on Table tools/Column tools. Are you using Power BI desktop(Default) or Power BI desktop for report server? If it is Power BI desktop(Default), what is the version?

snapshot of my desktop:

image
image1285×291 34.9 KB

4

I see. I’m using Power BI Desktop with Premium Licence. I think you have old version. My version- Version: 2.76.5678.782 64-bit (December 2019). If it is lower than that, please download new version and you may have to activate new features under Settings->Options->report setting and Preview features-> Turn on new filter settings.

6

We are using DirectQuery which does not have the same table and column tools. If I import the data, I can get the proper sorting, but we wish to use DirectQuery.

image
image1060×258 7.56 KB

7

@JWilliams,

See this video on how to sort month name in Power BI while using direct query

Hope this is helpful.

8

Hi @JWilliams , we’ve noticed that no response has been received from you since the 14th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

9

In order to sort by month name in direct query, we used following;

Dates1 = 
ADDCOLUMNS (
    CALENDAR ("1-jan-2017", "31-dec-2025"),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year",YEAR([Date]),
    "Monthnumber", FORMAT( [Date],"MMM"),
    "Monthnumberinteger", MONTH( [Date])
    )

and used Monthnumberinteger for sort by column.