Is there a way using DAX to know the name of a column a Table visual is sorted by

[Sergiy]

Hi,

I wonder is there a way using DAX to know the name of a column a Table visual is sorted by.

My final aim is to write a DAX code that will be changing a visual title each time a user clicks on a column title.

When a user clicks on SalesAmount column title, the visual title should change to "The data is sorted by SalesAmount".

When a user clicks on OrderDate column title, the visual title should change to "The data is sorted by OrderDate".

And so on.

Any ideas are greatly appreciated.

[jdbuchanan71]

While @MattAllington is correct, there is no way for DAX to read the sort state of a visual there is an option that might work for you but it will require a couple steps.

• Create a disconnected table with the list of measures you want to sort by, just as strings.
  

  Sort Measures = DATATABLE("Sort by",STRING,{{"Measure1"},{"Measure2"},{"SalesAmount"},{"Measure3"},{"Measure4"},{"Measure5"}})

• Add a slicer to your visual using the disconnected table
• Create a measure that reads the selected value from the slicer and returns that measure using SWITCH
  

  SortMeasure = 
  VAR SortingMeasure = SELECTEDVALUE ( 'Sort Measures'[Sort by], "SalesAmount")
  RETURN
  SWITCH(
      TRUE(),
      SortingMeasure = "Measure1",[Measure1],
      SortingMeasure = "Measure2",[Measure2],
      SortingMeasure = "Measure3",[Measure3],
      SortingMeasure = "Measure4",[Measure4],
      SortingMeasure = "Measure5",[Measure5],
      SortingMeasure = "SalesAmount",[SalesAmount],
      [Sales Amt]
  )

• Add that sort measure to the end of your table and sort by that.  When the selected measure changes, the vale fed into [SortMeasure] will change and the sorting will change.
  • You can make the column of [SortMeasure] in your table so narrow it doesn't show.
• Add a measure that generates your title.
  

  Sort Title = 
  VAR SortingMeasure = SELECTEDVALUE ( 'Sort Measures'[Sort by], "SalesAmount")
  RETURN "This table is being sorted by " & SortingMeasure

[Sergiy]

@jdbuchanan71 , thank you for trying to find a way.

The solution you created implies that a user is expected to change the sorting column by clicking on a slicer and never on column titles.

I am afraid we can't direct a user to click on a slicer if he is accustomed to clicking on a column title. And if he does click on a column title then your solution won't work.

But anyway, thank you for paying attention to my question and trying to help!

[jdbuchanan71]

@Sergiy I was betting the solution wouldn't work, it is quite a change, but thought I would offer it up anyway.

Cheers!

[MattAllington]

In the latest version of PBI Desktop, it is possible to turn on a visual level "help" icon with a custom message (Tool tip).  You could turn this on.  When the user hovers over the ?, you can present them instructions on what to do. There is already an arrow icon indicating which column is sorted and in which direction.  

[Sergiy]

@MattAllington ,

>There is already an arrow icon indicating which column is sorted and in which direction.  

I'd add there is another way for a user to get to know which column is currently sorted and choose another one for a new sorting order:

What I wanted is just to add a bit more comfort to a user filling a visual title not only with a context information(columns that are sliced by, scope, etc.) but with a current sorting order.

Explanations, in a form of a Tool tip or something of the kind, of why a user shouldn't click on a column title nor use a menu provided by ellipses wouldn't add the comfort I thought of.

Nevertheless, thank you both for sharing your ideas.

If I ever come to a solution (I am aware the chances are low) I'll let you know here.

[MattAllington]

No, there is no access to the sort staus of a visual that is accessible by DAX. 

[Sergiy]

It would be useful if there was a way.

By the way, @MattAllington , thank you for "Six Sigma Control Charts" solution. I like it.