Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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?
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.
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.
@jirineoral, yeah, I don't work a ton with live connections but I figured that might be the case.
yes, can't enter data on direct Query.
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,
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |