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.
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 )marcorusso - Thanks again for your insight. I guess it is at least a "documentation" bug since the docs say that it returns a decimal number. Your documentation is apparently more accurate.
https://community.powerbi.com/t5/Issues/MEDIAN-documentation-error/idi-p/1322575#M60278
That's one of the reasons why we created DAX Guide, even though the main one is that we wanted structured storage of metadata not available otherwise (like context transition and other details that are not included in Microsoft documentation). However, we didn't want to copy or replace Microsoft documentation, so we include a link to that in every function - many examples there are useful.
marcorusso , RiskyBiscuts - Of course, this discussion has led to another one of my To **bleep** With Quick Measures.
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-MEDIAN/td-p/1322755
🙂
Maestro!
