topic Re: Sorting a range of number and letters in DAX Commands and Tips

Sorting a range of number and letters

carlosagarcia — Tue, 22 Feb 2022 14:21:02 GMT

Good morning everyone,

I am looking for a way to divide inventory locations into zones.

Our inventory locations start with row 0, shelf A, bin 1, example 0A1. each shelf has 5 bins, the shelfs go from A to J and we have 70 rows.

0A5 0B5

0A4 0B4

0A3 0B3 and so on until shelf J

0A2 0B2

0A1 0B1

Then

1A5 1B5

1A4 1B4

1A3 1B3 and so on until shelf J

1A2 1B2

1A1 1B1

When I sort by location, the result is row 0 first then I get all the 10s, 11s and so on until I get row 1, then all the 20s before I get to row 2.

I am looking for a way to create inventory zones where...

Zone 1 are rows everything in between 0A1 to 6J5

Zone 2 are rows everything in between 7A1 to 11J5

Zone 3 are rows everything in between 12A1 to 15J5 and so on.

Will this be possible?

Re: Sorting a range of number and letters

amitchandak — Tue, 22 Feb 2022 16:08:31 GMT

carlosagarcia , Try a new column like

first concert you column to 4 digit

column = format([Your column], "0000")

then

Switch( True() ,

[column] >= "00A1" && [column] <= "06J5", "Zone 1" ,

[column] >= "07A1" && [column] <= "11J5", "Zone 2" ,

[column] >= "12A1" && [column] <= "15J5", "Zone 3" )

Re: Sorting a range of number and letters

carlosagarcia — Tue, 22 Feb 2022 17:13:46 GMT

Hello Ami,

I cant get the new column to have 4 digits.

Looks like it is formated to text.

So the second part does not pick up the changes

Re: Sorting a range of number and letters

tamerj1 — Wed, 23 Feb 2022 06:03:30 GMT

Hi carlosagarcia
It is doable. Please confirm on which order you need the result to be.

Your zones end 15J5, what about the rest?
Please share sample file it available.

Re: Sorting a range of number and letters

tamerj1 — Wed, 23 Feb 2022 10:19:17 GMT

Hi carlosagarcia
Here is a sample file with the proposed solution https://www.dropbox.com/t/kdaoTHJT66w4n9vH
In the sample file I used DX code to generate a separate rows (Row, Shelf & Bin) but you can conveniently use Power Query (Add Column - From Example ) to do the same. Once the columns are seperate you can use DAX to create a new "Sorting by column"

Sort By Number = VAR Shelves = SWITCH ( TRUE, [Shelf] = "A", 1, [Shelf] = "B", 2, [Shelf] = "C", 3, [Shelf] = "D", 4, [Shelf] = "E", 5, [Shelf] = "F", 6, [Shelf] = "G", 7, [Shelf] = "H", 8, [Shelf] = "I", 9, [Shelf] = "J", 10 ) VAR Result = [Row] + Shelves * 100 + [Bin] * 10000 RETURN Result

Last step: select the location and from Column Tools - Sort By Column select the new column.

Please let me know if you need to create hierarchy and group by Zones. Thank you!

Re: Sorting a range of number and letters

carlosagarcia — Thu, 24 Feb 2022 16:58:00 GMT

Hello Ami, have you been able to review my question?

Re: Sorting a range of number and letters

tamerj1 — Thu, 24 Feb 2022 17:00:45 GMT

Hello carlosagarcia

have you had the chance to review my solution? Thank you

Re: Sorting a range of number and letters

carlosagarcia — Thu, 24 Feb 2022 17:10:49 GMT

Tamerj1,

I am so sorry I did not see your response, I just did a quick reply, let me test it right now.

Re: Sorting a range of number and letters

Anonymous — Fri, 25 Feb 2022 08:32:26 GMT

Hi carlosagarcia ,

In order to give you a suitable solution faster, could you please provide some sample data(exclude sensitive data) and the final result you want? And give examples of the sorting rules involved. If possible, a sample pbix file would be better. Thank you.

Best Regards