How to Sort Bar Chart by Month Number from Live Query but Not Show On Chart

[MorningSwim]

I have the following chart which I'm sorting by axis (via the ellipsis menu of the visualization) using a calculated column that gives me the month number:

I don't want to show the month number, I only want to keep showing the other two calculated columns (Name of Month and Year).

Is there anyway I can hide month number?

[kpost · Accepted solution]

 

I re-created your problem and was able to get the X axis to sort correctly per your specifications.

Make sure the visual is sorted according to the Month and Year column as well.

See the attached .pbix  file, perhaps you can reverse engineer it to see where you went wrong.

///Mediocre Power BI advice, but it's free///

[v-kathullac]

Hi @MorningSwim  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @MorningSwim  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @MorningSwim  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[MorningSwim]

Still not working, I was applying the solutions given to no avail, waiting for responses.

[kpost]

Create a Month and Year column like this:  

Month_And_Year = FORMAT([Date], "mmm yyyy")

Then create a separate month and year column you will use to SORT it, make sure to format this one as a whole number rather than text.

year_month = FORMAT([date], "yyyymm")

then use Month_And_Year in your visual on the X axis.

Best practice is to do this in a Date Dimension table that has an active relationship to other tables on the date field.

 

///Mediocre Power BI Advice, but it's free///

[MorningSwim]

I created a date table and made a relationship between by transaction table and the date table using the respective date fields. I clicked in the Data pane of power bi desktop the Year Month field to bring up the "Column tools" tab in the ribbon, and proceeded to sort on the Year Month field. The visualization refreshed but it didn't sort. If I click the ellipsis on the bar chart itself, it seems to always want to sort on one of the X-axis fields, and I can't uncheck it.

[MorningSwim]

So create a reference table (2 fields, month number and month name), join it on the custom column month number from my main table, and then sort on the reference table?

[kpost]

You can create these columns in whatever table you're already using, I'm just saying that Power BI best practice includes the creation of a Date Dimension table in your semantic model, with active connections to all of your other tables.  No need to use joins of any kind.

Ideally your date dimension table would have a column of dates which you use to create the active connections, then calculated columns based on that date field. It is these calculated columns that you then use in your visuals.

You could create one like so:

Date_Table = CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31))

That would create a Date Table with a single column, all dates between Jan 1 2020 and Dec 31 2030.

You then create the active connection between that date column and the date columns in all of your other tables.

The purpose is so that as you continue to bring in more data sources, you can more easily create metrics that use data from multiple tables.

example, you have a revenue and an expense table.  You want to graph, by month, profit.

You could create a single measure:

profit = COALESCE(SUM(revenue[dollars]), 0) - COALESCE(SUM(expense[dollars]), 0)

then use that measure in a visual, with the X axis being the calculated Month column from your Date Dimension table.

[MorningSwim]

Hi, I went ahead and created a date table, and on the x-axis assigned the field "Month Year" from the date table. However, I still cannot sort it correctly based on the "Year Month" field of the date table, even when I use Column tools and sort based on that column.

[kpost · Accepted solution]

 

I re-created your problem and was able to get the X axis to sort correctly per your specifications.

Make sure the visual is sorted according to the Month and Year column as well.

See the attached .pbix  file, perhaps you can reverse engineer it to see where you went wrong.

///Mediocre Power BI advice, but it's free///

[MorningSwim]

If I sort both the Date Table by column Month Year as well as the visual I get this:

This makes sense to me, because it's basically in alphabetical order, which is not how we want it. I can force it by adding the Year Month (YYYY-MM) field in the toll tip and then sorting by that, but that doesn't seem like it would be the right approach.

[kpost · Accepted solution]

You are correct that there is no need to add it to the tool tip.

Did you download the .pbix file I created for you?  It has everything done, all you need to do is compare and find the difference between your file and mine.

Check everything to make sure you're doing it the same way.  Check the data types of the columns, make sure Month_And_Year  is sorted by year_month, and that year_month is formatted as a whole number, check the connection between the date table and the fact table in your semantic model, etc.

[lbendlin]

You mention "Live Query"  and "calculated column"  in the same scenario.  That's not possible?

[MorningSwim]

My data connection is using a SQL Server, using direct query I believe. The columns I mentioned earlier are custom columns I created in Power BI, using a dax in the formula bar.