Sort by another column not working with day-of-week number

[melly_meow]

Hi all, 

I am having issues with trying to sort my DoW (Day of week) column by the DoW_N (day of week number) column (beginning on Sunday). It is giving me the error "We can't sort the 'Act_Dow' column by 'Act_Dow_N'. There can't be more than one value in 'Act_Dow_N' for the same value in 'Act_Dow'. There are some blanks in this column due to the nature of the data. I was successfully able to sort its sister fields, 'Agreed_Dow' and 'Agreed_Dow_N'. 

After searching on this forum, a user recommended that I put both problem fields into a table and I'd be able to easily see the issue. It looks identical to the sister columns and I'm even able to sort the table by 'Act_Dow_N'.

Screenshots below of what I see. The Pink is the Agreed table working perfectly sorting by 'Agreed_Dow_N'. The blue is my issue table where I can't sort the 'Act_Dow' by 'Act_Dow_N'. Any ideas on how to solve this? I am confused on how it is giving me this error when I see the same values for its sister table. 

[kpost · Accepted solution]

Create a date dimension table then add the columns you are talking about IE Day of week, day of week Number etc.

This will guarantee no blanks, and you won't have issues sorting.

Then create an active many-to-one relationship between the 'Date' column in your date table and the 'Date' column in your fact table.  Then, in your visuals, use the day of week and day of week number columns from your 'Date' table.

I attached a .pbix file containing the solution.  Notably it has the following:

Date Table:

Date_Table = CALENDAR(DATE(1990,01,01), DATE(2050,01,010))

0-indexed Weekday column beginning on Sunday

Weekday_Number = WEEKDAY(Date_Table[Date]) - 1

Day Name Column

Day_Of_Week_Name = FORMAT('Date_Table'[Date],"dddd")

And then some demo data to show the proper relationship between this date table and the demo data.  The day of the week column is also properly sorted, by the Weekday Number. 

///Mediocre Power BI advice, but it's free///

[JimmyJams]

Apologies for piggybacking on this post, but I'm a little stumped and hoping someone may have an explanation. 

Per this advice and many others from other posts I've created a date dimension table, made my day of week and the index columns, then created the relationship back to my fact table. 

Then in my table view, I have it sorting by the index column:

And yet my visual hasn't changed the day of week order. Any ideas what I'm missing here?

I created a new visual from scratch and it's ordering by alpha now, but the table view is still set to sort by the weekday numbering index column. 

Any ideas on what I'm missing?

[JimmyJams]

Annnd figured it out. Or at least figured something out at least. I needed to click on the weekday field inside of my data window on the right and then use the sort by from there. 

Hope this helps someone save some time!

[kpost]

Yes, this is the correct procedure.  This is precisely what I meant by "The day of the week column is also properly sorted, by the Weekday Number." in the solution, but I am realizing now that I should have explicitly stated what that means.

Excellent follow-up with screenshots.  Thank you for your contribution to the Power BI community!

[JimmyJams]

Other forum posts have suggested the way I originally tried, but glad to have gotten it figured out. Thank you for your contributions to the community!

[melly_meow]

Thank you! The date dimension table was the solution. I'd created dimension tables before but I didn't think that would be the issue for this particular problem since the sister fields were working okay. 

I ended up creating a reference table from my main table, deleting unnecessary columns, creating new columns using if/then functions to remove blanks/combine the columns, then deleted the original columns. This is the first time I've created a dimension table this way by removing some source columns. I'm hoping it will still work once the data is refreshed next week. 

[kpost]

Having a single Date Dimension table generated from scratch (No missing dates), with an active relationship to the 'Date' column in all of your fact tables is best practice, because as your dataset continues to grow, (more fact tables are added), it enables you to easily combine data from many different sources into a single measure in a visual broken down by date/week/year etc,  and overall makes creating your reports much easier for more reasons than I can list in this comment.

You can also use the 'Date' column from the Date Table in a single slicer on a report page that can then filter everything on the page in the selected date range, even if the fact tables being used to create the report page have no relationships with each other.

Glad the solution helped.  Happy Data Mining.

///Mediocre Power BI advice, but it's free///

[melly_meow]

Got it. Thank you for the thorough explanation! 

I went ahead and I created a new date dimension table. Thank you for all of your help!