Forum Discussion
Helper IIIALL(Column) doesn't work if Column is created in query editor
By any chance have you used the "sort by column" feature on any of the columns involved?
If so, read this page (along with the solution).
http://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/
If not, can you post a sanitised pbix file that exhibits the problem?
On your heretical thought:
I would say DAX calculated columns certainly have a place.
If there is no difference in difficulty between adding a column in the Query Editor and DAX, I would favour the Query Editor. That way I can see the final version of the table in the Query Editor, and it is easier to maintain the table. For example, I would avoid running the risk of adding a column in the Query Editor that was also added as a DAX calculated column.
If I need to rely on relationships in the data model or measures in order to calculate a column, I may favour DAX.
In the end, both types of columns are updated at refresh, so users won't see any difference, and I imagine that differences in performance due to the compression difference would not be noticeable unless tables were very large.
Super UserBy any chance have you used the "sort by column" feature on any of the columns involved?
If so, read this page (along with the solution).
http://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/
If not, can you post a sanitised pbix file that exhibits the problem?
On your heretical thought:
I would say DAX calculated columns certainly have a place.
If there is no difference in difficulty between adding a column in the Query Editor and DAX, I would favour the Query Editor. That way I can see the final version of the table in the Query Editor, and it is easier to maintain the table. For example, I would avoid running the risk of adding a column in the Query Editor that was also added as a DAX calculated column.
If I need to rely on relationships in the data model or measures in order to calculate a column, I may favour DAX.
In the end, both types of columns are updated at refresh, so users won't see any difference, and I imagine that differences in performance due to the compression difference would not be noticeable unless tables were very large.
IanRThat was it!
If the column is sorted then the sort by column has to be included in the ALL.
My successful measure now looks like this:
Est Value (All Time Frames Query) = CALCULATE([Est Value (Open)], ALL(OpportunitySet[PurchTimeFrame], OpportunitySet[PurchTimeFrameSort]) )- IanR
And thanks.