Forum Discussion

RiskyBiscuts's avatar
RiskyBiscuts
Advocate I
6 years ago
Solved

"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 s...
  • Greg_Deckler's avatar
    Greg_Deckler
    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.

  • marcorusso's avatar
    marcorusso
    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.