I try to sort the month name by the number of the month inside the semantic model but its not reflacting in my power bi report??
Hi @Asaad_AlSharif ,
Regarding the inconsistency of the “Sort By Column” display on the service side and the desktop side, I understand your problem and have tested it:
Here is my test data:
I can reproduce your problem, in service I set the MonthName column to be sorted according to MonthNo, but after connecting this semantic model in desktop, I can see that it is not displayed according to my setting.
In power bi desktop, when connecting to the semantic model of the service, although sort by column shows incorrectly, when I use it in the visual object, it sorts by the sort field I set in the service. So I think it's a display issue that doesn't affect normal work.
When I choose to sort by MothName, if I don't set sort by column in service, it should sort alphabetically. However, since sort by MonthNo column is set in service, when I select sort by MonthName ascending in the visual object, I can see that the month names on the X-axis are sorted correctly.
Please test it in your Power BI Dektop and see if it works fine as I did.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Thank you for your detailed response and efforts, Yang.
I appreciate the tests you've conducted, and I understand the display issue on the Power BI Desktop when linked to the semantic model from the service. However, I've noticed that when using a Slicer, the sorting doesn't work as intended, even though it functioned correctly in earlier versions of Power BI.
To work around this, I am currently prefixing the month names with their respective numbers (e.g., "01-January", "02-February"). This method ensures the slicer sorts the months correctly. I thought it might be useful to mention this workaround in case others face a similar issue and are looking for a temporary solution.
from pyspark.sql import SparkSession
from pyspark.sql.functions import format_string, udf, concat_ws, date_format,col, trim, substring_index, regexp_replace,regexp_extract, when, expr, ltrim, rtrim, date_format, dayofmonth, weekofyear, month, quarter, year
from pyspark.sql.types import StringType
def translate_month(month_number):
months_in_arabic = {
1: "January", 2: "February", 3: "March", 4: "April",
5: "May", 6: "June", 7: "July", 8: "August",
9: "September", 10: "October", 11: "November", 12: "December"
}
return months_in_arabic.get(month_number, "")
month_udf = udf(translate_month, StringType())
# Define transformations
df_new_cases = df_cases.select(
concat_ws(" ", format_string("%02d", month("date_opened")), month_udf(month("date_opened"))).alias("MonthName"),,,,,,
)
Best regards, Asaad AlSharif
Thank you for your detailed response and efforts, Yang.
I appreciate the tests you've conducted, and I understand the display issue on the Power BI Desktop when linked to the semantic model from the service. However, I've noticed that when using a Slicer, the sorting doesn't work as intended, even though it functioned correctly in earlier versions of Power BI.
To work around this, I am currently prefixing the month names with their respective numbers (e.g., "01-January", "02-February"). This method ensures the slicer sorts the months correctly. I thought it might be useful to mention this workaround in case others face a similar issue and are looking for a temporary solution.
from pyspark.sql import SparkSession
from pyspark.sql.functions import format_string, udf, concat_ws, date_format,col, trim, substring_index, regexp_replace,regexp_extract, when, expr, ltrim, rtrim, date_format, dayofmonth, weekofyear, month, quarter, year
from pyspark.sql.types import StringType
def translate_month(month_number):
months_in_arabic = {
1: "January", 2: "February", 3: "March", 4: "April",
5: "May", 6: "June", 7: "July", 8: "August",
9: "September", 10: "October", 11: "November", 12: "December"
}
return months_in_arabic.get(month_number, "")
month_udf = udf(translate_month, StringType())
# Define transformations
df_new_cases = df_cases.select(
concat_ws(" ", format_string("%02d", month("date_opened")), month_udf(month("date_opened"))).alias("MonthName"),,,,,,
)
Best regards, Asaad AlSharif
Hi @Asaad_AlSharif ,
Regarding the inconsistency of the “Sort By Column” display on the service side and the desktop side, I understand your problem and have tested it:
Here is my test data:
I can reproduce your problem, in service I set the MonthName column to be sorted according to MonthNo, but after connecting this semantic model in desktop, I can see that it is not displayed according to my setting.
In power bi desktop, when connecting to the semantic model of the service, although sort by column shows incorrectly, when I use it in the visual object, it sorts by the sort field I set in the service. So I think it's a display issue that doesn't affect normal work.
When I choose to sort by MothName, if I don't set sort by column in service, it should sort alphabetically. However, since sort by MonthNo column is set in service, when I select sort by MonthName ascending in the visual object, I can see that the month names on the X-axis are sorted correctly.
Please test it in your Power BI Dektop and see if it works fine as I did.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
