Sort by using Visuals API

[Zubair_Muhammad]

Hi,

How can I perform the sort operations (as shown in the pic below) inside the custom visual using Power BI visuals API?

for example if I am creating a Table visual and want to sort Table by clicking on the headers/buttons etc

I can use sort function of javascript. But sometimes a column is sorted by another column in Power BI. Javascript's sort function will not respect that. 

@dm-p @v-rzhou-msft @v-yiruan-msft 

[dm-p]

Hi @Zubair_Muhammad,

In custom visuals, sorting just affects the order of the visual's dataset as supplied from the host (as this is set outside the sandbox using the standard header) and there's no API to determine which column or measure is actually the subject of the sort. 

If you want control of sorting (short of a feature request to add such an API, which would admittedly be very useful) then it's probably better to use an option of implicit or custom and managing sorting through your visual events and /or properties. The Violin Plot uses this approach, although the data roles are reasonably predictable to manage. If your data roles are flexible then this is more tricky - I might approach by creating interactive elements to sort by a particular column, sort your view model's dataset based on that, and persist a property containing which field in your view model you're using so that it will remain in-place when the visual is re-initialised (you could remove the property from view during enumeration if you just want to make it accessible from code rather than by user).

Regards,

Daniel

[Zubair_Muhammad]

Thanks Daniel @dm-p 

Actually we can determine which column has been used for sorting from the columns' metadata.

These 2 properties appear if column has been used for sorting

My predicament is how to know if this column is sorted by another column. For example you will add index column to sort months column {Jan, Feb, Mar}  so that they are not sorted alphabetically as {Feb,Jan,Mar}

In a table visual, users intuitively click on the headers to sort rather than using the native option to sort from visual header. Thats why I was wondering if there is an option there or not in the API

The native Table/ Matrix visual does that

[dm-p]

Hey Zubair, and thanks very much for teaching me something new (to me!) 🙂

Being as I wasn't too informed on my previous post, I'd take what I say next with a grain of salt, but I haven't been able to find this myself in any previous (or current) projects and have deferred to using the visual header to manage.

I've never seen details on sort by column metadata for a field in the API, and if the sort values aren't in the dataset, they could not be used internally. It is likely that core visuals have more direct access to the data model as they are not sandboxed, or there are APIs that have not been promoted to the custom visuals SDK. 

If there isn't an API to bring in from core, something new on the visual host would be ideal for this, i.e.:

interface IVisualHost {
    ...
    applySort(columns: DataViewSortColumn[]) /* New */
}

interface DataViewSortColumn {
    queryName: string,
    direction: 'ascending' | 'descending'
}

(the param doesn't have to be an array, but sorting by multiple columns would be neat to achieve also)

Either way, I think it's better asked directly to the visuals team, or creating an issue in the GitHub repo for some dedicated assistance.

Regards,

Daniel

[Zubair_Muhammad]

Thanks Daniel @dm-p 

I really like this suggestion of applysort in IVisualHost.
I emailed to pbicvsupport as well for help. Waiting for their reply now.

[v-rzhou-msft]

Hi @Zubair_Muhammad 

Please kindly share your workaround in your Post if you find the solution to your problem. This will help many other people with the same problem like yours.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.