none
Having issues sorting groups by a sort order from another table

    Question

  • I am new to PowerPivot and couldn't quite find the answer I needed in other posts, so excuse me if there was something I didn't see. I have a fact table which includes a department ID and I have a dimension table that includes the department ID and the order in which it should be sorted when it is grouped. I am doing things like summarizing the number of visits to a particular set of departments in a hospital and the departments need to be listed under the hospital in a specific order, which is a column in the dimension table. 

    I created a column on my fact table that uses the RELATED function to retrieve the sort order field from the dimension table. (I got that from a set of instructions about sorting dates by month number). However, when I try to assign the new column as the sort by column for my department group, I get an error telling me that there is an incorrect one to many relationship. In other words, it thinks there are multiple departments that have the same sort order or something like that. I assure you that the sort order dimension has one record per department and one sort order per department and they are not shared. 

    Obviously I could drag and drop my groupings into the right order, but I don't want to have to do that every time I produce the report. 

    Can anyone give me explicit instructions? I am using imported Excel spreadsheets as my data sources. 

    Thursday, March 27, 2014 5:15 PM

Answers

  • I'm a little confused... does your department dimension have a field specifying department name and a field specifying department grouping? 2 distinct fields?

    You shouldn't need to worry about having a sort column in the fact table.

    You only need it in the dimension.  Just be sure to use only fields from the dimension when creating pivots or charts.

    As you mention, sort columns always must have a 1 to 1 unique relationship with the columns they will be sorting. If there is a hierarchy in your data such as departments being part of a group then the group would also require its own sorting column in addition to the sort column used for departments..

    Thursday, March 27, 2014 9:04 PM
    Answerer

All replies

  • I'm a little confused... does your department dimension have a field specifying department name and a field specifying department grouping? 2 distinct fields?

    You shouldn't need to worry about having a sort column in the fact table.

    You only need it in the dimension.  Just be sure to use only fields from the dimension when creating pivots or charts.

    As you mention, sort columns always must have a 1 to 1 unique relationship with the columns they will be sorting. If there is a hierarchy in your data such as departments being part of a group then the group would also require its own sorting column in addition to the sort column used for departments..

    Thursday, March 27, 2014 9:04 PM
    Answerer
  • Hi,

    Do you have any duplicates in column which you would like to sort with connection to column you want data to sort with? .sounds to me like there is a problem with unique identification of member you would like to sort by column you want to sort it with. Eg. if you want to sort month in format (201401,201301) by column month number (01) it won't work. But composite key for sorting could help. If so consider creating composite key for sorting purposes as combination of columns (you can get by simple concatenation of the two columns)

    Jiri


    Jiri Neoral

    Thursday, March 27, 2014 9:30 PM
  • Thank you for your reply Mike. I think the problem is that I am using fields from the fact table instead of from the dimension. The client that I am working for right now insists on having views that are completely de-normalized instead of having true fact and dimension tables so I have some data elements that should be in a dimension but aren't. One of these is the description of the department and I am using that as the row value. If I load up the department dimension and use that, along with the sort order in the department dimension, then that should work?


    Diana Bodell

    • Marked as answer by D. Bodell Friday, March 28, 2014 5:15 PM
    • Unmarked as answer by D. Bodell Friday, March 28, 2014 5:16 PM
    Friday, March 28, 2014 5:14 PM
  • Thanks for your reply, Jiri. As I mentioned in response to Mike's answer, I think the problem lies in the fact that I am not using a data element from a department dimension table in my pivot, but instead pulling it from a de-normalized view that includes the dimensional data. One more reason not to organize data that way, even if it is easier for report developers.

    Diana Bodell

    Friday, March 28, 2014 5:15 PM