Forum Discussion
"Expressions that yield variant data-type..." error when your Col. is of "Whole number" data type
This one is an odd one. I am well aware what this error ("Expressions that yield variant data-type cannot be used to define calculated columns.") entails but I am not sure why its occurring in this specific case. Some background on this, I appended two tables, one sourced from a standard SQL Sever DB, and the other from Azure Data Lake Storage Gen2 (I combined various files, and did a count from one a column called deviations). The end goal is to create a calculated column of the median of the column labeled "unique deviations". It does have to be a calculated column.
Both data sources have the data type as "whole number", and when I appended them, I made sure I set the data type to "Whole number", removed any errors, nulls, ect. When I troubled shoot the data from the SQL source, as a calculated column for Median it works just fine. So I suspect its the datalake table, where I used some M to calculated the counts. The confusing part is that the Total calculated by the table yields a whole number, so I am not sure why this is occuring?
All I can think of is that somehow the other table isn't formatted right, but I have no idea how to fix this. I have looked at other reltaed posts, but both my source column have been formated to whole numbers, so I am lost. All I can think is that its something about the table from the data lake or that the data type doesnt really work as it should. Help?
Warm Regards,
RiskyBiscuts - I tried using PERCENTILE.EXC and PERCENTILE.INC but same behavior. I also tried every variation of MEDIANX I could think of as well as even throwing CALCULATE in here and there. Couldn't get it to work.
But, where there is a will, there is a way.
Column 4 = VAR __Table = ADDCOLUMNS(ADDCOLUMNS('Table (19)',"Above",COUNTROWS(FILTER('Table (19)',[Unique Deviation]<EARLIER([Unique Deviation]))),"Below",COUNTROWS(FILTER('Table (19)',[Unique Deviation]>EARLIER([Unique Deviation])))),"Diff",ABS([Above]-[Below])) VAR __Min = MINX(__Table,[Diff]) RETURN MAXX(FILTER(__Table,[Diff]=__Min),[Unique Deviation])Brute force, manual Median column. Updated PBIX is attached.
I'm not sure it is a bug.
MEDIAN and MEDIANX return a VARIANT: https://dax.guide/median/Therefore, they cannot be used in a calculated column as-is.
You can convert the result, though:
CONVERT ( MEDIAN ( Table[Column] ), INTEGER )This way, you can use it in a calculated column. You keep the risk of a failed conversion to INTEGER.
I think that the reason why MEDIAN/MEDIANX is variant is because they were originally meant to be used with any data type including strings, even though now they are not compatible with such a data type as an input, so I really don't know why they return variant instead of numbers... We should ask to Microsoft about this.
15 Replies
Greg_Deckler sorry mate, I tried posting it but it kept giving me a html error. It is the below.
Median = MEDIAN('Project Data'[Unique Deviation])
RiskyBiscuts - Well, I can completely reproduce this with just a small dataset. See attached PBIX, Table 19 (below sig). Really kind of odd. Works perfectly fine in a measure but as a calculated column it bombs miserably. But other aggregators work, SUM, AVERAGE, etc. Just MEDIAN does not seem to like column context. Seems like a bug but maybe marcorusso will weigh in on this one.
Also, MEDIANX variations act the same way.
I'm not sure it is a bug.
MEDIAN and MEDIANX return a VARIANT: https://dax.guide/median/Therefore, they cannot be used in a calculated column as-is.
You can convert the result, though:
CONVERT ( MEDIAN ( Table[Column] ), INTEGER )This way, you can use it in a calculated column. You keep the risk of a failed conversion to INTEGER.
I think that the reason why MEDIAN/MEDIANX is variant is because they were originally meant to be used with any data type including strings, even though now they are not compatible with such a data type as an input, so I really don't know why they return variant instead of numbers... We should ask to Microsoft about this.
RiskyBiscuts - What is the formula for that Median column or is there one?
