Sort column not exposed in Transform data

[BratKat]

Have a column measure that is not exposed in Transform, is there and FORMAT command I can use in this to make sure it sorts correctly, tried other suggustions using Tooltips but those do not work. Thank you 

MonthDay = 'Work Items'[Team Date 01].[Month] & " Day " & DAY('Work Items'[Team Date 01])

[tamerj1 · Accepted solution]

Hi @BratKat 

You ca create a sort by column 

MonthDayNumber =
VALUE (
MONTH ( 'Work Items'[Team Date 01] ) & DAY ( 'Work Items'[Team Date 01] )
)

Select the MonthDay column, go to column format - sort by column and select the MonthDayNumber column. 

[tamerj1 · Accepted solution]

Hi @BratKat 

You ca create a sort by column 

MonthDayNumber =
VALUE (
MONTH ( 'Work Items'[Team Date 01] ) & DAY ( 'Work Items'[Team Date 01] )
)

Select the MonthDay column, go to column format - sort by column and select the MonthDayNumber column. 

[BratKat]

Tried this however returned some strange results, slicer not returning correct dates..?

[tamerj1]

@BratKat 

Which column are you placing in x-axis?

[BratKat · Accepted solution]

The MonthDayNumberYear measure or column.  

[BratKat]

Got this working, forgot to add "Year" in the data item, so now it sorts correctly, thank you. 

[tamerj1]

@BratKat 
No, just keep the original YearMonth column in the x-axis and use the "sort by column" option to sort the YearMonth column by the YearMonth column number. Go the table view, select the YearMonth column then from the "sort by column" option, select the YearMonthNumber

[BratKat]

Already did that, its usually when I move the Slicer that it stops sorting correcty, using the "between" option in the slicer. 

[BratKat]

When the column is used in a column graph and month days are displayed, these are not in correct order even if sorted correctly in the graph, need to add sorting or format to the measure\column code above so it stays sorted. A A slicer is used to select days of the month and the measure\column is used in the graph as the X-axis.

The days start out correctly then is out of order. 

Thank you 

[BratKat]

Additional info: 

We already have a column that has Dates in our data set, Team Date 01 that has the dates of each item in our dataset. 

This is used in the X-axis 

MonthWeek = 'Work Items'[Team Date 01].[Month] & " Week " & WEEKNUM('Work Items'[Team Date 01])

and Team Date 01 is used in a Slicer. 

[DOLEARY85]

Hi,

any measures or columns created in report view are caclulated after the import of data and therefore will not appear in transform. What is it you're looking to do with the column in transform?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍