Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Week sort

Hi all!   please help me with the following,   I have a column Week , which contains begging and end of week, I was planning to sort it by date, but power bi returns an error "Sort by another co...
DAX
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    You are digging in the wrong location. When to stop repeating? That brings you back to the original problem of ranking using the correct granularity. This is an infinite loop and will lead to nowhere.

    The issue is that your business logic wasn't clarified so I had to guess. You can achieve this using

    day_rank =
RANKX (
    'Calendar',
    'Calendar'[Date] - WEEKDAY ( 'Calendar'[Date], 2 ),
    ASC,
    DENSE
)

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Anonymous 
Try

 

day_rank =
RANKX ( 'Calendar', 'Calendar'[Week],, ASC, Dense )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hi tamerj1 ,

     

    thanks for your quick response!

     

    sorry, I forgot to mention, that won't work coz Week is a text values, and ranking it doesn't give correct (compared to date) result, as you can see here, 14th week of April 2019 is ranked 1st , even though, there are weeks before it

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Anonymous 
      Aha. Then please try

      day_rank =
RANKX (
    'Calendar',
    VALUE ( SUBSTITUTE ( 'Calendar'[Week_Sort], "_", "" ) ),
    ASC,
    Dense
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        tamerj1 ,

         

        same problem,

        one value from Week column links to two values from Day_rank column