Forum Discussion
Sorting Issues for a Bar Chart
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.
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.
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
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.