Column Sort Order Issue

[bcdobbs]

Hi,

I have a central dataset that we maintain using Tabular Editor via XLMA end point.

I have a very simply dimension table:

In tabular editor I have the sort by column set:

However when I then connect to it in Power BI Desktop  and create a basic line chart, the sort order is not taken account of:

Have checked the sort order on the visual:

Am I missing something obvious either in tabular editor or PBI Desktop.

[ToddChitt]

Can you verify that your Sort By Column is correct by checking it in the Column Tools menu item of the same name?

And can you show the entire visual?

[bcdobbs · Accepted solution]

Hi Todd, 

Thanks for replying.

I'm connecting live to a data set so the sort by column option in power bi desktop is greyed out:

Would expect that as it's a property of the dataset.

I've actually just fixed it (need to work out what's going on under the service in the engine though).

By recording the query in PBI desktop for the visual I found that the sort column is being thrown into the query which is good (means it knows it is the sort column) but second in the sort order:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"AMA"}, 'Site'[Academy Code])

  VAR __DS0FilterTable2 = 
    TREATAS({"2021/2022"}, 'Calendar'[Academic Year])

  VAR __DS0FilterTable3 = 
    TREATAS({"Projected"}, 'Grade Types'[GradeType])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'CAF Slot'[CAFSlot],
      'CAF Slot'[DisplayOrder],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      "Basics_4_", 'KS4 Statistics'[Basics 4+]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(1001, __DS0Core, 'CAF Slot'[CAFSlot], 1, 'CAF Slot'[DisplayOrder], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'CAF Slot'[CAFSlot], 'CAF Slot'[DisplayOrder]

Going back to tabular editor I had CAFSlot flagged as a Key column (which it is). Removing that flag fixed the issue.