Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MP_123
Employee
Employee

sort by column

hi

is there any way to sort table\card\slicer by column that are not in the table?

i have a 'month' column in 1 for january,2,3,4... i cannot create new column by using format(month,mm) because of the direct query, and there is no way to format in the pbi to just month

i can create new column as : if month=1 then January.. etc, by the sorting will be alphabetic, and i want to sort by 1,2,3...

do you have a solution? thanks!<3

9 REPLIES 9
jirineoral
Post Patron
Post Patron

@MP_123

Hi,

I'm afraid not, unless sorter key (month number this case) doesn't exist in original source. If it does, you could use sort by column option on modeling tab. Howerver as far as I know (and test), you can't sort persisted column by calculated column. So if you're connected using live connection to SQL Database and you have that option, it would be easiest to create view with sorter column and connect to it instead of table.

 

Jiri

Greg_Deckler
Super User
Super User

Can you create a separate "Enter Data" query like:

 

Month,Sort

January,1

February,2

March,3

 

Have "Month" sort by "Sort". Relate the two tables on "Month" and use the "Month" from this new table instead of from the other table?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

This idea occured to me too. But Enter data is grayed out when you're using live connection 

Can you use the "hidden column" trick. Basically, add both the month name and the month number as columns in the table and then shrink the month number column down so you can't see it and then use the visualization controls "..." to sort by the hidden column.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

problem with that is if you pin this visualisation to a dashboard the hidden month number column unhides it self!

@MP_123 Are you hitting a Table or View in your DQ? If you use Views when accessing data via DQ you can easily handle any additional data requirements for your modeling from the source database.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@jirineoral, yeah, I don't work a ton with live connections but I figured that might be the case.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

yes, can't enter data on direct Query.

@MP_123

 

Since you are using Direct Query, you can't do any modeling within Power BI Desktop. There's no other way better than add this "description column" in source database.

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.