Search code examples
powerbicalculated-columnsmedian

Power BI DAX - Running the MEDIAN or MEDIANX formula for a calculated column gives error message

I have been trying to generate a calculated column within Power BI Desktop using the MEDIAN formula to get the median age. I checked to see if there were any inconsistencies like text data or blank data within the Age column but, did not find any such instances. The error message is as follows -

Expressions that yield variant data-type cannot be used to define calculated columns.

Error Message The data used in the analysis is hosted on data.world

Any help will be highly appreciated.

Solution

  • This is a pretty strange situation but I think I found the explanation on the Microsoft Community Forum.

    When the underlying column is of data type Whole Number, MEDIAN function returns a variant data type because it may return a Whole Number when there is no interpolation or a Decimal Number when there is interpolation. While measures can be of variant data type, calculated columns must be of a single data type, hence the error. To force MEDIAN to always return Decimal Number, change the expression to MEDIANX(Table1, [Column2] * 1.0).

    Another fix would be to convert the Age column to the decimal number type rather than the whole number type. I also found wrapping with VALUE work.