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///

[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!