Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Power BI Sort by Column issue

Hi,
I opened a blank report and added a new table:

Table = DATATABLE("Name", STRING, "Ord", INTEGER, {
{"X",1},
{"Y",2},
{"X",3}
})
When trying to sort column Name by column Ord I get an error: We can't sort the 'Name' column by 'Ord'. There can't be more than one value in 'Ord' for the same value in 'Name'.
So I delete the last entry in the table code:
Table = DATATABLE("Name", STRING, "Ord", INTEGER, {
{"X",1},
{"Y",2}
})
But I still have the same error when trying to sort Name by column Ord. However, if save the report, close Power BI and reopen the report then it works. Bug?
Moreover, after sorting Name by Ord, I can add the {"X",3} back in the table code and it works. Then why not woring from the beginning?!
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    There was a bug in version 2.139.1733.0 - use a different version.  Your example works fine with 2.139.2054.0 , for example

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous,

    Thanks for the reply from lbendlin, I could not roll back to an earlier version to reproduce your issue, try to update to a new version like lbendlin mentioned and check if it could work on your side.

     

    However, I think we could discuss about why it does not work from the beginning. I think it is a cache issue.

     

    • Power BI might not be refreshing its internal state or metadata when you modify the DataTable() function. This can cause it to retain the old logic even after you remove them.

    • Power BI enforces a constraint that a column used for sorting must have a one-to-one relationship with the column being sorted. When duplicates are present, this constraint is violated, and the error occurs.

    • Saving, closing, and reopening the report likely forces Power BI to rebuild its internal cache or metadata, which resolves the issue.

    If you don't want to update the version, I suggest you use a calculated table instead, which is more reliable.

    Table2 = 
UNION(
    ROW("Name", "X", "Ord", 1),
    ROW("Name", "Y", "Ord", 2),
    ROW("Name", "X", "Ord", 3)
)

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    There was a bug in version 2.139.1733.0 - use a different version.  Your example works fine with 2.139.2054.0 , for example

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous,

    Thanks for the reply from lbendlin, I could not roll back to an earlier version to reproduce your issue, try to update to a new version like lbendlin mentioned and check if it could work on your side.

     

    However, I think we could discuss about why it does not work from the beginning. I think it is a cache issue.

     

    • Power BI might not be refreshing its internal state or metadata when you modify the DataTable() function. This can cause it to retain the old logic even after you remove them.

    • Power BI enforces a constraint that a column used for sorting must have a one-to-one relationship with the column being sorted. When duplicates are present, this constraint is violated, and the error occurs.

    • Saving, closing, and reopening the report likely forces Power BI to rebuild its internal cache or metadata, which resolves the issue.

    If you don't want to update the version, I suggest you use a calculated table instead, which is more reliable.

    Table2 = 
UNION(
    ROW("Name", "X", "Ord", 1),
    ROW("Name", "Y", "Ord", 2),
    ROW("Name", "X", "Ord", 3)
)

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!