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.
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
🙂
Greg_Deckler / marcorusso that kid crying was me until you guys found me a solution, and I agree, I think M$ does this intentionally so we can remember math, hehehe.
