Forum Discussion
"Expressions that yield variant data-type..." error when your Col. is of "Whole number" data type
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.
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.
marcorusso this is also a great solution in fact, this helps me answer the question I also asked Greg_Deckler. Elegant!
CONVERT ( MEDIANX(
FILTER(
ALL('Project Data'),
[UnitType] = EARLIER([UnitType])
),
[Unique Deviation]
), INTEGER )
Greg_Deckler correct, I could get average no problem, but median is a miserable fail! I looked at your pbi file, so thank you for reproducing it! I am willing to bet you couldnt even use the merged table in a data model, reminds me of something that happened in my past. Hopefully a solution exists.
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.