Which DAX functions are correct?

[Anonymous]

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!

[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. 

[daXtreme]

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.

[johnt75]

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.

[Anonymous]

@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]

@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.

[Anonymous]

@Greg_Deckler  

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:

[Anonymous]

@Greg_Deckler  The given "correct" answer cannot be correct. This is why I am asking here if MY answer which I suggest in the original post is correct.

[Greg_Deckler]

@Anonymous The right answer is to dispose of all of that DAX and start over. 

[Anonymous]

@Greg_Deckler  In general, I would agree. But I am in the situation to have to pass the PL-300 Exam and as far as I understood, this has been an original question in an earlier version of that exam and I try to use these questions for preparation. Therefore, I am interested to understand which solution is correct (independent of the question if the code is best practice or not).

[Greg_Deckler]

@Anonymous As near as I can dechiper, then it *should* be FILTER, AND, CALCULATE. I hate to say anything is correct about that DAX.

[Anonymous]

@Greg_Deckler 

As far as I can say, the "solution" is either FILTER, AND, ALL or FILTER, AND, CALCULATE.

Problem: If you ask me, both do not work.

Particularly, the task is to code a calculated table, this means that the measure has to give either "small", "medium" or "large".

But both answers do not do that and, moreoever, technically the arguments are not fitting, because FilterSegment is a table then and the DISTINCT function gives a column....

Maybe I should not insist to find the answer... 

[Greg_Deckler]

@Anonymous Actually, I tested this locally on a sample dataset, FILTER, AND, CALCULATE does in fact work. It only works though because the DISTINCT brings back a single row table that CALCULATE converts to a scalar value. 100%, FILTER, AND, CALCULATE works.

[Anonymous]

@Greg_Deckler  OKAY! Then I do not understand the following:

(1) 'Transaction Size'[Transaction Size] is a column, namely the column

Transaction Size

Small

Medium

Large

Applying DISTINCT to it, should just give the same column.

(2) FilterSegment is a table since FILTER returns a table.

(3) If I apply CALCULATE to these two arguments, I really do not see how this works.

[Greg_Deckler · Accepted solution]

@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