Forum Discussion
Which DAX functions are correct?
Hi, all together!
I am referring to Question #44 which you can find here
I have a bunch of questions to this exercise!
I think the solution should be
FILTER
AND
ALL
but have some problems with that (especially with the ALL).
Problem 1: The arguments in the AND function are columns, that is, all
'Transaction Size'[Min], 'Transaction Size'[Max] and also SalesTotal
are columns.
Does this mean that AND goes through each entry of the columns separetely (how else should one compare two columns by < or >)?
Problem 2: Is ALL correct as the last option? I think CALCULATE is not possible as DISTINCT(column) returns a column and not an expression as needed for CALCULATE. But now comes my but:
If ALL is correct, then what is RETURN? It should be a table but the task was to build a calculated column...
Really confusing!
Anonymous Well, no sane human being would construct that measure that way. All you need to do is MAXX(FilterSegment,[Transaction Size]). You have to read up on CALCULATE extremely closely to understand why this works: CALCULATE – DAX Guide. Some CALCULATE only DAX try hard worked overtime to get CALCULATE into that formula. It's utter nonsense. But, because CALCULATE evaluates the 'Transaction Size'[Transaction Size] within the context of FilterSegment, which is a single row table, then the DISTINCT brings back a single row table of the [Transaction Size] that happens to correspond to the context of the single row table in FilterSegment, which is whatever transaction size the sales happen to fall into. It's black box, stupidly complicated nonsense and doesn't have to be nearly that difficult or obfuscated. MSHGQM - Don't Use CALCULATE! - YouTube
16 Replies
johnt75Super User
Pretty sure it is CALCULATE. You can use a table expression like DISTINCT inside CALCULATE if you know it is only going to return a table with 1 row and 1 column - that gets converted to a scalar value. Their code is relying on the filtering to make sure that only 1 row gets returned from the Transaction Size table.
johnt75 Ok, but I really do not see why this should be the case here as
DISTINCT( 'Transaction Size'[Transaction Size]) is a column
and
FilterSegment is a table..
- johnt75
"DISTINCT( 'Transaction Size'[Transaction Size]) is a column" - yes it is, but in this instance the code has been crafted to try and guarantee that it will return a column of only one row. as another user posted, when you pass a table with a single column and a single row into a function which requires a scalar value the DAX engine automatically does the conversion.
As another example, look at the difference between LASTDATE('Date'[Date]) and MAX('Date'[Date]). LASTDATE returns a table with a single row and a single column, MAX returns a scalar value. But you can still use LASTDATE as a scalar value ( even though you shouldn't ), because DAX will automatically make the conversion. The conversion does not work both ways though, it will not convert a scalar value into a table. You can check this on dax.do by running
EVALUATE MAX ('Date'[Date] ) EVALUATE LASTDATE ('Date'[Date] )The first query will fail, because it does not return a table. The second query will work, because it does return a query.
"FilterSegment is a table." - all filters are tables, and you can use any table as a filter as long as it has the correct lineage.
- Greg_Deckler
Community Champion
Anonymous It's CALCULATE, ALL, FILTER although I would never construct that measure that way. I'm not even sure that the formula is actually valid or would even work as FILTER is going to return a table of values. Super awful DAX.
FILTER
Returns a table that represents a subset of another table or expression.
I do not understand why
CALCULATE
ALL
FILTER
is correct.
Why is the first CALCULATE?
'Transaction Size' is a table.
- Greg_Deckler
Anonymous Well, those are the correct answers according to if you Reveal Solution. I'm not saying any of it is correct. It's all trash DAX imho. But, according to the site, those are the "right" answers:
daXtremeSolution Sage
You cannot use a table function as the first argument to CALCULATE. It must be a scalar expression. A table expression will result in an error, even if the function would return one value (one col and one row) in the context. You have to use CALCULATETABLE in this case.
- tamerj1
Anonymous
Actually CALCULATE works just fine with VALUES and DISTINCT as long as the table returns one row. The engine automatically converts this table to a scalar value.
