Sorting Issues for a Bar Chart

[CraigSchulz]

I've read some similar posts but none seem exactly like mine, so here goes. I have a couple of issues.

1st, help with sorting suggests I can click on the elipses in a visual and there will be a menu item there for "sort on column" with the additional ability to sort A to Z or Z to A. The only menu items I get when I click the elipses are: Export Data, Show Data, Remove, or Spotlight.

I have a column of data that compares budget to actual dollars. I did have it sorting on this column (sort by column seemed to work then) but my client wanted the numbers reversed, he wants the positive numbers on the top with negative numbers on the bottom. I created a new column which is budget to actual dollars time -1, and called it SortOrder. I tried to use that with the Sort by Column selector, but when I do that I get a message that I can't sort with a button that already is sorted on those data either directly or indirectly. Help on both issues would be most appreciated.

[edhans · Accepted solution]

Regarding the first sort, is a field from a DIM table (like an item master, customer master) on the axis of the bar chart? the sort by options wont' show up if you are using a field from the FACT table (like a customer key in a sales table). In that example, use the customer number/name from the Customer master table.

On the second sort, in a table I assume, get rid of the fake column where you muitiplied by -1. Sort by the comparison column. then sort again. It toggles from A..Z to Z..A each time you sort.

[edhans · Accepted solution]

Regarding the first sort, is a field from a DIM table (like an item master, customer master) on the axis of the bar chart? the sort by options wont' show up if you are using a field from the FACT table (like a customer key in a sales table). In that example, use the customer number/name from the Customer master table.

On the second sort, in a table I assume, get rid of the fake column where you muitiplied by -1. Sort by the comparison column. then sort again. It toggles from A..Z to Z..A each time you sort.

[CraigSchulz]

Thanks for the quick response. I'll show my ignorance (and if you have the time I would appreciate enlightenment) by admitting I don't really know what a DIM table is vs. a FACT table. I'm pulling these data from an Excel spreadsheet.

I did have account code on the bar chart axis, and, upon closer scrutiny, I realized the axis took those as ordinal numbers and made the axis as a sequence of numbers incremented by one, your typical numerical axis. I changed the data type of the account code from whole number to text and whalah, the axis looked right and the sorting worked. It even showed up in the elipses drop down menu. I will take you advice and jettison the calculated column and use the actual column instead. I appreciate your help. 

Again anything you can teach me about DIM and TABLE would be most appreciated.

[edhans · Accepted solution]

Here is  a decent article on it.

In summary, a FACT table are your facts - the sales data for example. A sales table might have a customer number, item number, invoice number, invoice date, quantity, and amount.

A DIM table are your dimensions, or sometimes called master tables. So a customer master/DIM table would have your customer number, customer name, address, zip code, phone number, etc.

In Power BI and Excel with Power Pivot, you want those in separate tables. Before Power Pivot, you had to put all of that in one big flat table so Excel's Pivot Table function worked. 

With Power BI and Power Pivot, you want those in separate tables, then you join them. This page has a pretty good overview of joining and how you'd use a Star Schema pattern (FACT table in the middle, DIM tables around it) when joining. Power BI works best with a Star Schema.

[CraigSchulz]

Thanks for all your help. I have a couple of quick follow-up questions I hope you can help me with.

How does Power BI know if a table is a FACT table or a DIM table?

Is a Date table considered a DIM table? Seems like it would be, but I have real difficulty sorting using fields from my Date Table. For example, in my Date table I have day of the week number, 1-7, Sunday being the seventh day. I also have a Day Name field in the Date Table, Monday through Sunday. When I create a visual with the Day Name on the axis the days are not in order, Monday is in the middle of the list for some reason. I tried to sort using Day of the week, but it doesn't seem to work.

- Craig

[edhans]

Once you set up relationships in Power BI, it knows a DIM table is on the One side of the relationship and a FACT table is on the Many side. It figures that out based on the unique values in the field being joined. A date table is a DIM table.

As to sorting things by some sort of date (month name, week name, day name, whatever) you generally need to use the Sort by Column feature on the modeling tab. I usually have other fields in my date table that are strictly numeric so I can sort calendar names by something other than alphabetically.

[CraigSchulz]

Yes, I am with you on the number vs name thing. While I have Day Name as the axis labels, I also have a numeric column in my date table that indicates 1 for Monday through 7 for Sunday. Then I select the field 'Day of Week', the numeric field, go to "Sort by Column" in the Modeling menu item. There, it shows "Day of Week" as the default (why, I'm not sure). Even if I click that again to try to make the sort change, nothing happens. The day names are still unsorted. Actually, now that I look at it, that is not exactly true, the data are sorted in descending order by the value being charted. If I change the axis to be the numeric 'Day of Week' it sorts just fine, 1 through 7, in order.

[edhans]

If it is sorted descending, just click sort again in the desktop app before publishing. I think the July 2018 update added a clear A-Z/Z-A sort selection, but prior to that you just hit sort again and it toggled from A-Z and Z-A. Not 100% sure on the exact way to do it. I'm on my mac right now, and no PBI desktop app to check.

[edhans]

Once relationships are set up in the model, Power BI/DAX knows it is a FACT table if it is on the Many side of a relationship, and a DIM table if it is on the One side. The Date table is a DIM table.

On sorting by dates, you generally have to use the Sort By column feature otherwise Power BI tends to sort alphabetically. I often create separate sorting only fields in my date table for this reason.

[edhans]

Quick followup: The UI for sorting has changed quite a bit in the July 2018 desktop app. There are now separate buttons to sort ascending/descending, and to sort by specific columns. See the video this month. Sorting UI changes are discussed starting around 35:40.