Distinct inside a calculate function

[mp390988]

Hi,

I was going through a question on the PL300 exam and noticed the solution has explicitly written a distinct as the first argument of a calculate function as marked in the snippet below. I didn't even know this was possible as my understanding is that the distinct returns a column and the first argument of a calculate should be a scalar expression not a table expression. Furthermore, all of this is meant to be inside a calculated column as the question mentions this so how does each row of a calculated column store a table expression?

Original Question:

SOLUTION - please see where I have marked in yellow.

It just seems so odd but it seems to work as I tested it. Maybe it works because only 1 value is being returned by the distinct inside here:

var Result = CALCULATE(DISTINCT(transaction Size[Transaction Size]), FilterSegment)

Thanks,

[v-echaithra]

Hi @mp390988 ,

Could you please confirm if the issue has been resolved or not. If a solution has been found helpful, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Best regards,
Chaithra E.

[v-echaithra]

Hi @mp390988 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @mp390988 ,

In DAX, the distinct function typically returns a table, but within the calculate function, it works by modifying the context to return a scalar value. In this scenario, the distinct function, when applied to the context of SalesTotal in the Transaction Size table, ensures that only a single, distinct value either small, medium, or large is returned for each row in the Sales table, which fits within the scalar requirement for a calculated column. calculate processes this table expression in context, ensuring that only a single value the appropriate transaction size is returned for each row. This approach is valid for classifying sales based on predefined categories and works as expected in calculated columns.

Best Regards,
Chaithra E.

[OwenAuger · Accepted solution]

Hi @mp390988 

You've hit the nail on the head 🙂 A single-column table with at most one row is treated as a scalar value if the context requires. In the case of zero rows, the value returned is BLANK.

See here.

In this case, the configuration of the 'Transaction Size' table ensures that the result will have at most one row.

If 'Transaction Size' were misconfigured and had rows with overlapping [Min,Max] ranges, this expression for Sales[Transaction Size] could result in an error.