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 column" ,

stating that there are multiple values in Column Date for Column Week, which I was trying to sort,

 

Therefore , I created column Week_sort that technically should be working, but it doesn't coz at the end of the year a row from column Week links to two different values from column Week_sort ,

any ideas how to solve this problem?

 

my initial idea was to:

day_rank =
RANKX(
    'Calendar',
    'Calendar'[Date],,ASC)

which returns ranking of days,

and then "somehow" make DAX to repeat value from row above if row values in column Week are equal?

unfortunatelly i couldn't figure it out on my own  

 

p.s. this table is created in DAX so I can't fix the problem in PQ

 

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
)

     

12 Replies

  • 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

    Hello Anonymous , if you are looking to only sort, then use the following added column

    Week sort = FORMAT(Calendar[Date],"yyyyww")

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      hi Anonymous ,

       

      that's not gonna work 🙂