topic Re: Sort column not exposed in Transform data in DAX Commands and Tips

Sort column not exposed in Transform data

BratKat — Mon, 24 Apr 2023 21:07:16 GMT

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])

Re: Sort column not exposed in Transform data

DOLEARY85 — Tue, 25 Apr 2023 07:26:04 GMT

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 👍

Re: Sort column not exposed in Transform data

BratKat — Tue, 25 Apr 2023 14:04:10 GMT

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

Re: Sort column not exposed in Transform data

BratKat — Tue, 25 Apr 2023 19:30:57 GMT

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.

Re: Sort column not exposed in Transform data

tamerj1 — Tue, 25 Apr 2023 19:56:59 GMT

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.

Re: Sort column not exposed in Transform data

BratKat — Wed, 26 Apr 2023 14:21:47 GMT

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

Re: Sort column not exposed in Transform data

tamerj1 — Wed, 26 Apr 2023 14:28:17 GMT

BratKat

Which column are you placing in x-axis?

Re: Sort column not exposed in Transform data

BratKat — Thu, 27 Apr 2023 14:44:01 GMT

The MonthDayNumberYear measure or column.

Re: Sort column not exposed in Transform data

tamerj1 — Thu, 27 Apr 2023 15:27:34 GMT

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

Re: Sort column not exposed in Transform data

BratKat — Thu, 27 Apr 2023 21:20:46 GMT

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

Re: Sort column not exposed in Transform data

BratKat — Mon, 01 May 2023 14:24:07 GMT

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