Forum Discussion
Distinct inside a calculate function
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 forSales[Transaction Size]could result in an error.
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.