Reply
Regular Visitor
Posts: 27
Registered: ‎02-15-2016
Accepted Solution

Slicer Sort Issue

Hi i have a table like

 Field1    field2

  1            qrs

  2            abc

  3            xyz

  4            lmn

 

i want to use a slice and display the field2 but i want it to be sorted by field one.

yes i have used the SORT option on field1 in the Data section, but it doesn't work in the slicer.

any ideas?

sincerely

Nik


Accepted Solutions
New Contributor
Posts: 544
Registered: ‎10-27-2015

Re: Slicer Sort Issue

@SNik, your verbiage gives me pause as to how you applied the sort. I will err on the side of being overly explicit, as I am unable to reproduce the behavior you describe. When I set a Sort By attribute on a field in PBI Desktop, that sort order is respected in any slicer I create.

 

  1. Select [field2]
  2. With [field2] selected choose Sort By Column
  3. In drop-down menu that appears, place a check mark next to [Field1]

As I said, the specific verbiage you used gave me pause, and it sounded like you might be selecting [Field1] and applying a sort order to it.

 

 

Sort By Column is essentially saying "sort the currently selected field using the order defined by the associated values in another field."

It is not saying "apply the values of the selected field to another when sorting."

 

I hope I am being clear, and again I apologize if I'm only telling you things you already know. I just want to make sure we're performing the same steps and getting different results.

View solution in original post

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Slicer Sort Issue

@SNik Return a single value per combination. All "Under 1000" will = 1, all "[01  -2  K]" will = 2

row  price pricebracket

1      1       Under 1000

2      1       Under 1000

3      1       Under 1000

4      1       Under 1000

5      2      [01  -2  K]

View solution in original post


All Replies
New Contributor
Posts: 544
Registered: ‎10-27-2015

Re: Slicer Sort Issue

@SNik, your verbiage gives me pause as to how you applied the sort. I will err on the side of being overly explicit, as I am unable to reproduce the behavior you describe. When I set a Sort By attribute on a field in PBI Desktop, that sort order is respected in any slicer I create.

 

  1. Select [field2]
  2. With [field2] selected choose Sort By Column
  3. In drop-down menu that appears, place a check mark next to [Field1]

As I said, the specific verbiage you used gave me pause, and it sounded like you might be selecting [Field1] and applying a sort order to it.

 

 

Sort By Column is essentially saying "sort the currently selected field using the order defined by the associated values in another field."

It is not saying "apply the values of the selected field to another when sorting."

 

I hope I am being clear, and again I apologize if I'm only telling you things you already know. I just want to make sure we're performing the same steps and getting different results.

Regular Visitor
Posts: 27
Registered: ‎02-15-2016

Re: Slicer Sort Issue

Hi Greggyb

 Please do not apologize for anything you are doing grate in helping people online and i know how hard and time consuming it is becasue i was doing this on the SSIS forum, anyways thank you and thank you to the Microsoft team putting these forum out to help people.

I am self learnning Power Bi myself and trying to make some real world examples and getting myself ready for the future of the PowerBi and Microsoft new technologies and trying to be a futur contributer at this forum like you in the future like as i did for SSIS .

 

Anyways i did what you asked to be more exact i am trying to doa a PriceBanding like

  [Price]     [Price Bracket]

     1               Under 1000

     2               Under 1000

     .

     .

  1000           [1000 - 2000]

  1001           [1000 - 2000]

 

you get the idea, so what i do is

1- click on the [Price Bracket] field

2- Click on the "sort by Column" ribon on top

3- select the [Price] field

 

and i get an error of .....

 

We Cannot sort the [Price Bracket] column by [Price] field. you can not have more than one value in the [Price] field for the same value in [Price Bracket]. choose a different column for sorting or update the data in Price$

 

.......

the strage thing is that it is saying the the Price$ field is not unique if you check the M code it is

 

 

the M code is like

 

let
    Source = {1 .. 100000 },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Price"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price", Int64.Type}}),
    #"Price Bracket" = Table.AddColumn(#"Changed Type", "PriceBand1", each 
if   -1 > [Price] then "Unknown"
  else if     0 <= [Price] and [Price] <  1000 then "Under 1000"
  else if  1000 <= [Price] and [Price] <  2000 then "[01  -2  K]"
  else if  2000 <= [Price] and [Price] <  2500 then "[02  -2.5K]"
  else if  2500 <= [Price] and [Price] <  3000 then "[02.5-3  K]"
  else if  3000 <= [Price] and [Price] <  3500 then "[03  -3.5K]"
  else if  3500 <= [Price] and [Price] <  4000 then "[03.5-4  K]"
  else if  4000 <= [Price] and [Price] <  4500 then "[04  -4.5K]"
  else if  4500 <= [Price] and [Price] <  5000 then "[04.5-5  K]"
  else if  5000 <= [Price] and [Price] <  5500 then "[05  -5.5K]"
  else if  5500 <= [Price] and [Price] <  6000 then "[05.5-6  K]"
  else if  6000 <= [Price] and [Price] <  6500 then "[06  -6.5K]"
  else if  6500 <= [Price] and [Price] <  7000 then "[06.5-7  K]"
  else if  7000 <= [Price] and [Price] <  7500 then "[07  -7.5K]"
                else if  7500 <= [Price] and [Price] <  8000 then "[07.5-8  K]"
  else if  8000 <= [Price] and [Price] <  8500 then "[08  -8.5K]"
                else if  8500 <= [Price] and [Price] <  9000 then "[08.5-9  K]"
  else if  9000 <= [Price] and [Price] <  9500 then "[09  -9.5K]"
                else if  9500 <= [Price] and [Price] < 10000 then "[09.5-10 K]"

  else if 10000 <= [Price] and [Price] < 11000 then "[10-11K]"
                else if 11000 <= [Price] and [Price] < 12000 then "[11-12K]"
  else if 12000 <= [Price] and [Price] < 13000 then "[12-13K]"
                else if 13000 <= [Price] and [Price] < 14000 then "[13-14K]"
  else if 14000 <= [Price] and [Price] < 15000 then "[14-15K]"
                else if 15000 <= [Price] and [Price] < 16000 then "[15-16K]"
  else if 16000 <= [Price] and [Price] < 17000 then "[16-17K]"
                else if 17000 <= [Price] and [Price] < 18000 then "[17-18K]"
  else if 18000 <= [Price] and [Price] < 19000 then "[18-19K]"
                else if 19000 <= [Price] and [Price] < 20000 then "[19-20K]"

  else if 20000 <= [Price] and [Price] < 25000 then "[20-25K]"
  else if 25000 <= [Price] and [Price] < 30000 then "[25-30K]"
  else if 30000 <= [Price] and [Price] < 35000 then "[30-35K]"
  else if 35000 <= [Price] and [Price] < 40000 then "[35-40K]"
  else if 40000 <= [Price] and [Price] < 45000 then "[40-45K]"
  else if 45000 <= [Price] and [Price] < 50000 then "[45-50K]"


  else if 50000 <= [Price] and [Price] < 60000 then "[50-60K]"
  else if 60000 <= [Price] and [Price] < 70000 then "[60-70K]"
  else "[70K +")
in
    #"Price Bracket"

 

 

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Slicer Sort Issue

@SNik Both columns must have a unique value in order for one to be sorted by the other. For example. For your Price Bracket of "Under 1000" - you can only have one corresponding value in the Price column. ( in this case "1"). I'm way new to "M", but based on your example you expect the output to have more than one Price value for "Under 1000". 

Unique in this instance means that if the value of "Under 1000" is sorted by "1", it cannot be sorted by any other number.

 

Regular Visitor
Posts: 27
Registered: ‎02-15-2016

Re: Slicer Sort Issue

Hi Eno

Then what is the solution? one of the most important thing that the bussines wants in the BI world is to slice and dice by Band and brakets, how can we do this in Power BI?

sincerely

Nik

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Slicer Sort Issue

@SNik Return a single value per combination. All "Under 1000" will = 1, all "[01  -2  K]" will = 2

row  price pricebracket

1      1       Under 1000

2      1       Under 1000

3      1       Under 1000

4      1       Under 1000

5      2      [01  -2  K]

Highlighted
Regular Visitor
Posts: 27
Registered: ‎02-15-2016

Re: Slicer Sort Issue

Hi Grgee and Eno

Thanks for everything you both had the right solution, thank you

my next question will be in another post related to this about how to generate the SORT Order number in M laguage

thank you both again

Be Frequent Visitor
Frequent Visitor
Posts: 14
Registered: ‎02-26-2016

Re: Slicer Sort Issue

Hello!

 

I think this is what I am needing for my situation, but I need a little extra information for this newbie. Smiley Happy

 

I have odometer readings for vehicles. (My dataset is all about vehicles -make, model, etc.)

 

What I want to do is create a slicer that will allow someone to see all vehicles with 0-10K odometer reading. Right now, someone would have to click every value under 10K to see that. (At least, from my perspective as I don't know another way.) It seems that this "M" language method is the best way to accomplish this. 

 

If I am understanding correctly, I need to create another table like this:

 

Row    Odometer    Odometer Range

1          1                 Under 10K

2          1                 Under 10K

 

Where my confusion comes in is in the Odometer [Price] column. I don't understand why it is the same value over and over again. It seems that SNik understands why, but this newbie doesn't. 


Can someone walk me through exactly how to do this? Where to go in Power BI Desktop and what to click, etc? Any and all help is greatly appreciated!

 

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Slicer Sort Issue

@Be There are a couple things at play here.

1) Your request is a little different, but could include the solutions described in this post. This thread is speaking about how to "order" a column by a different column. In your case, you may want to order your new group if it doesn't show up as expected. If that is the case, you would add the "price" column to be a distinct value that corresponds to each of your groups. 1= 0-10K, and each row would get a 1 for that group. The reason for this, is that each row needs the same 1 to 1 match in order to apply the "sort by column" function.

2) For the "how to group" question, you can do this during the data load in M code (as example above), but it may be easier in your case as a newbie to create this using a calculated column. This would be after you already loaded your data, you would use the DAX language to create an "IF" or "SWITCH" DAX function to check for and substitute the actual value for a group value in the calculated column.

 

Really rough example, right click on your table - "New Column", the DAX code bar will pop down and you can enter a calculation similiar to this. 

= SWITCH(TRUE(),
AND([Odometer] >=0, [Odometer] <=10000), "0 to 10K"
AND([Odometer] >=10001, [Odometer] <=30000), "10 to 30K"
AND([Odometer] >=30001, [Odometer] <=60000), "30 to 60K"
"60K+")

 

- Look up the specific syntax here to be sure, and just build out your groups as needed