Missing value after sort by column

[Anonymous]

Hello,

My table contains the value and I added 4 more items with formula using - Var .... Return Switch (4 items I want to put), because otherwise 4 calculated formula won't show up in the table. The original table with 4 items show the whole value perfectly, although the sequence has changed. After I changed the item order by index using 'sort by column' the 4 items are missing. Is there anyone who had a same issue? Thank you.

Best regards,

Sichang

[Jason6]

https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/solution/ 

https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...

This is the solution for measure values becoming blank or incorrect when we sort a column by another column

To fix the issue we need to include all(column name which is used to sort by)

So if Column A is sorted by Column B, 

Our measure should include 

calculate( expresession, filter criteria, all(ColumnB))

Hope this helps someone 🙂 

[ravisharora003]

This is a fantastic solution. Thanks a lot. Not even AI tools like Copilot and Gemini was able to crack this

[Anonymous]

Hi! Were you ever able to resolve this? Same thing's happening to me. Thanks!

[Anonymous]

Hello,

I attached the screen shot below. I sorted the item - 1 by index column to make the right sequence.

But after sort by column, 4 items don't show the value anymore (these 4 items are formula: ROCE, EBITDA%, CM1%, Spec.CM1). I think it's because of the new measure I make: Item 1 - Act. I make a variable Item1new and return switch to present calculated item. (the calculated 4 items are not part of the original item - 1 so that's why I tried out in this way.)

Basically, the table is working before sort by column, but after sorting the item - 1, Item 1 - Act doesn't show the value for the calculated results.

It would be great if you can give me another approach or fix my formula. Thank you.

Best regards,

Si Chang

[amitchandak]

Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks