Forum Discussion
"Expressions that yield variant data-type..." error when your Col. is of "Whole number" data type
- 6 years ago
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.
- 6 years ago
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 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.
- RiskyBiscuts6 years agoAdvocate I
Greg_Deckler you are fantastic! That was it! It gave me 41 as it should! You've made my day, thank you.
- Greg_Deckler6 years agoCommunity Champion
RiskyBiscuts - Still think it's a bug, but somehow I have started a trend of creating DAX equivalent calculations for DAX functions. MOD, RANKX, time "intelligence", etc. Guess I will add MEDIAN to that list! 🙂
- RiskyBiscuts6 years agoAdvocate I
Greg_Deckler I actually have another question related to this one, do I start a new question?
(I need to find median but this time per "Unit Type" (a different column, but that UniqueDeviation bug causes that error...)