Forum Discussion
Which DAX functions are correct?
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
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..
Super User"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.