Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
@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.
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.
@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]
Hello!
I think this is what I am needing for my situation, but I need a little extra information for this newbie. 🙂
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!
@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
@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.
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.
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"
@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.
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
@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]
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
4 | |
3 | |
2 | |
2 | |
2 |
User | Count |
---|---|
10 | |
7 | |
3 | |
2 | |
2 |