Forum Discussion
Missing value after sort by column
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
5 Replies
https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/solution/
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 🙂
This is a fantastic solution. Thanks a lot. Not even AI tools like Copilot and Gemini was able to crack this
Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
ThanksHello,
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
