ALL(Column) doesn't work if Column is created in query editor

[IanR]

Hi,

I have been struggling with some basic filters that use ALL(Column_Name) in a measure to remove the filter on a particular column. Sometimes it works and sometimes it doesn’t. I think I have finally worked out what the problem is; if the column inside the ALL(…) is created in the Query Editor then it doesn’t work – the filter is not removed; if the column is from the source data or is created as a calculated column in DAX then it works – the filter on that column is removed.

I am trying to use ALL(Column) to remove the filtering on Purchase Time Frame, which is a field in our CRM. The values in CRM are numeric (0, 1, 2, 3 and 4). These have to be converted to human readable text for the report to be useful (Immediate, This Quarter, Next Quarter etc.). I need to remove the filter so that I can derive a total value to calculate percentages (of the estimated revenue 40% is likely immediately, 20% in this quarter etc.).

My first pass at this used the SWITCH function in DAX to create a calculated column that would return the text version of the purchase time frame. When I used this column as a row in the table and in my measure I got the desired result. This is my measure:

Est Value (All Time Frames Calc Col) = CALCULATE([Est Value (Open)],
					ALL(OpportunitySet[PurchTimeFrame(CalCol)])
					)

I then went on a (very good) training course where it was recommended that we do not use calculated columns but use the Query Editor to create new columns instead, the reason being that calculated columns are less likely to be included in the file compression. That’s great but using a column created in Query Editor as a row in the table and in a new version of my measure I do not get the desired result, the filter on the column is not removed. As far as I can see there is nothing different about the DAX:

Est Value (All Time Frames Query) = CALCULATE([Est Value (Open)],
					ALL(OpportunitySet[PurchTimeFrame])
					)

I’ve tried the same thing using the original numeric field from the source as a row in the table and in an otherwise identical measure and that worked. The conclusion I have to draw is that ALL(Column) doesn’t work if Column was created in Query Editor.

Am I missing something?
Heretical thought: Is using calculated columns really so bad?

Thanks

Ian

[OwenAuger · Accepted solution]

@IanR

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.

[IanR]

And thanks.

[IanR]

That 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])
          )

[v-yulgu-msft]

Hi @IanR,

I didn't encounter this problem. In my test, the ALL() function worked fine for those columns created in the Query Editor. 

Would you please provide some sample data and tell me how to reproduce your scenario? And please provide the image of your expected result and the unexpected result you have got.

Regards,
Yuliana Gu