Sorting with BISM model


  • Having an issue with week numbers sorting in PowerView report, which is based on a BISM model.  Found this posting - the solution does not work.

    Have a table which the first column is datetime called [PK_Date] which the table is sorted by.  Created another column using following formula ="WK "&WEEKNUM([PK_Date], 2) column is called [Week_Number].  When l place the field Week_number as the column on a matrix in power view it is sorted WK 10, WK 11, WK 9 which is incorrect.

    Tried to sort  [Week_Number] by [PK_Date], get an error message.  Any suggestions?  Since cannot set sorting what l can do other than accept this issue?

    Friday, March 22, 2013 10:52 AM

All replies

  • Please post error message
    Friday, March 22, 2013 1:41 PM
  • Error message l understand what the error message is saying it makes sense

    "Cannot sort Week_Number by PK_Date because at least one value in Week_Number has multiple distinct values in PK_Date. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region."

    Does not help with my issue, as in how do l ensure the data is sorted on the report in the order l want it to be sorted in.  Yes a workaround is WK 9 2013, the idea behind WK 9 is that the user knows it represents WK9 in 2013.  As in the case there are only 4 weeks worth of data being displayed.

    Friday, March 22, 2013 2:05 PM
  • Hi Bob,

    Sorry for the delay.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support

    Thursday, March 28, 2013 6:48 AM
  • Hi,

    I think its because [Week_Number] is interpreted as string so it sorted as string based. From the other forum posting, I think it mentioned about create month number as number (or int) so it can be sorted as number correctly.

    Try to remove 'WK' from [Week_Number] and make it into number(int) for column type.

    Christian HL
    Microsoft Online Community Support

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, March 28, 2013 7:38 AM
  • Thanks for the suggestion, not sure if the client will like the removal of the "WK" prefix.  I agree yes the value is being sorted as string since the value starts with a letter.

    I've been looking at this more closely and realised that what l was to do is not possible.  One point l missed out the date table that is being used is over from 2011 to 2015.  So when l use the expression to create the WK numbers, they will repeat since there is no year number associated with the value.

    Two possible solutions (l think)

    1) create a smaller date table, which only spans 52 weeks, then could use the same formula ="WK "&WEEKNUM([PK_Date], 2).  This should allow sorting by the date column, as the WK value should not be repeated.  Not tried so not sure about this.

    2) create an expression and only look at values window a 52 week window with the date table l currently have in the BISM model.   Probably using current date and going back x weeks and forward x weeks, to get the value.

    Seems like l have still to learn some things about the BISM model and its limitation and quirks :-)  The challenge is l have spent much more time with SSRS and so want to use the similar techniques.

    Friday, March 29, 2013 3:25 PM