Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a table in SQL which I import using Direct query in power BI. I have a text column with values as Inverter 1, Inverter 2 ..... Inverter 16. The issue is I am not abloe to sort it like below.
Inverter 1
Inverter 2
.
.
.
Inverter 16
Instead it is automatically sorting in the below order,
Inverter 1
Inverter 10
Inverter 11
.
.
.
Inverter 9
Please look into the pic for more details.
Kindly help.
Thanks
Krishnamurthy
Solved! Go to Solution.
Hi @Kolumam,
Here I made a test to achieve your goal.
1. Connect a table of SQL server database using direct query.
2. Add a custom column using this formula in Query Editor of Power BI Desktop.
Custom = if [test] = "Inverter 1" then 1 else if [test] = "Inverter 2" then 2 else if [test] = "Inverter 3" then 3 else if [test] = "Inverter 4" then 4 else if [test] = "Inverter 5" then 5 else if [test] = "Inverter 6" then 6 else if [test] = "Inverter 7" then 7 else if [test] = "Inverter 8" then 8 else if [test] = "Inverter 9" then 9 else if [test] = "Inverter 10" then 10 else if [test]="Inverter 11" then 11 else if [test]="Inverter 12" then 12 else if [test]="Inverter 13" then 13 else if [test]="Inverter 14" then 14 else if [test]="Inverter 15" then 15 else 16
3. Sort the test column by Custom. Then we can get the result as you want.
Hope this will be helpful!
Regards,
Frank
Hi All,
I am trying to sort by Inverter1, Inverter 2 etc...but it is not sorted properly. Please help to fix it as this is done using direct query.
Hi Kolumam,
Since its in text format try using a 2- digit number , I.e Inverter 01, Inverter 02 etc. while defining the switch statement.
Hi @Kolumam,
Here I made a test to achieve your goal.
1. Connect a table of SQL server database using direct query.
2. Add a custom column using this formula in Query Editor of Power BI Desktop.
Custom = if [test] = "Inverter 1" then 1 else if [test] = "Inverter 2" then 2 else if [test] = "Inverter 3" then 3 else if [test] = "Inverter 4" then 4 else if [test] = "Inverter 5" then 5 else if [test] = "Inverter 6" then 6 else if [test] = "Inverter 7" then 7 else if [test] = "Inverter 8" then 8 else if [test] = "Inverter 9" then 9 else if [test] = "Inverter 10" then 10 else if [test]="Inverter 11" then 11 else if [test]="Inverter 12" then 12 else if [test]="Inverter 13" then 13 else if [test]="Inverter 14" then 14 else if [test]="Inverter 15" then 15 else 16
3. Sort the test column by Custom. Then we can get the result as you want.
Hope this will be helpful!
Regards,
Frank
Hi,
But I think custom columns are not allowedin Direct query. It gives an error saying - "This operation is not allowed in Direct query, switch to import mode"
Hello @v-frfei-msft and @Kolumam
This advice doesn't work for me because I am not allowed to create a custom column in the data view. Doing so forces me to switch to Import. Therefore I can only do this in the report view. I've got a column with the range defined, and another column with the sort order of that range defined but I keep getting an error when I sort the former by the latter:
Here are the columns I've created, trying to sort Employee Segment by Employee Segment Order ascending:
Thanks for any assistance you can give.
Best,
Carly
Hi @Kolumam,
Does that make sense? If so , could you please mark this answer as solution?
Regards,
Frank
Hi Frank @v-frfei-msft, along these lines, can you show me how to create an indexed column using between statements then sort on that column?
I have a total measure based on two fields. Once it's in a visual I need to sort by the custom text descriptors. but I can't get it to work. Normally I would add a custom column or table with an index to sort using the in query editor but I can't do it that way with Direct Query.
Measures:
# Employees0 = SUM(sales_marketing_report[ORGEmployees0Number])
# Employees1 = SUM(sales_marketing_report[ORGEmployees1Num])
# Employees Total = sales_marketing_report[# Employees0] + sales_marketing_report[# Employees1]
I've tried this but it gives me a cicular dependence error:
Employee Bands = SWITCH (
TRUE (),
IF ('sales_marketing_report'[# Employees Total] >= 0 && 'sales_marketing_report'[# Employees Total] <= 5, TRUE, FALSE) , "0 to 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 6 && 'sales_marketing_report'[# Employees Total] <= 9, TRUE, FALSE) , "6 - 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 10 &&'sales_marketing_report'[# Employees Total] <= 25, TRUE, FALSE) , "10 - 25"
, IF ('sales_marketing_report'[# Employees Total] >= 26 && 'sales_marketing_report'[# Employees Total] <= 100, TRUE, FALSE) , "26 - 100"
, IF ('sales_marketing_report'[# Employees Total] >= 100, TRUE, FALSE) , ">100"
)
My goal is to set up visuals with counts, % and totals based on those bands/ranges:
Hi Frank @v-frfei-msft, along these lines, can you show me how to create an indexed column using between statements then sort on that column?
I have a total measure based on two fields. Once it's in a visual I need to sort by the custom text descriptors. but I can't get it to work. Normally I would add a custom column or table with an index to sort using the in query editor but I can't do it that way with Direct Query.
Measures:
# Employees0 = SUM(sales_marketing_report[ORGEmployees0Number])
# Employees1 = SUM(sales_marketing_report[ORGEmployees1Num])
# Employees Total = sales_marketing_report[# Employees0] + sales_marketing_report[# Employees1]
I've tried this but it gives me a cicular dependence error:
Employee Bands = SWITCH (
TRUE (),
IF ('sales_marketing_report'[# Employees Total] >= 0 && 'sales_marketing_report'[# Employees Total] <= 5, TRUE, FALSE) , "0 to 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 6 && 'sales_marketing_report'[# Employees Total] <= 9, TRUE, FALSE) , "6 - 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 10 &&'sales_marketing_report'[# Employees Total] <= 25, TRUE, FALSE) , "10 - 25"
, IF ('sales_marketing_report'[# Employees Total] >= 26 && 'sales_marketing_report'[# Employees Total] <= 100, TRUE, FALSE) , "26 - 100"
)
My goal is to set up visuals with counts, % and totals based on those bands/ranges like the matrices below.
Thanks for any help!
Carly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |