cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

"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 specific case. Some background on this, I appended two tables, one sourced from a standard SQL Sever DB, and the other from Azure Data Lake Storage Gen2 (I combined various files, and did a count from one a column called deviations). The end goal is to create a calculated column of the median of the column labeled "unique deviations". It does have to be a calculated column. 

Median.PNG

 

Both data sources have the data type as "whole number", and when I appended them, I made sure I set the data type to "Whole number", removed any errors, nulls, ect. When I troubled shoot the data from the SQL source, as a calculated column for Median it works just fine. So I suspect its the datalake table, where I used some M to calculated the counts. The confusing part is that the Total calculated by the table yields a whole number, so I am not sure why this is occuring?

Count works.PNG

 

All I can think of is that somehow the other table isn't formatted right, but I have no idea how to fix this. I have looked at other reltaed posts, but both my source column have been formated to whole numbers, so I am lost. All I can think is that its something about the table from the data lake or that the data type doesnt really work as it should. Help? 

 

Warm Regards,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

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.

 

View solution in original post

14 REPLIES 14
Highlighted
Super User IV
Super User IV

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@RiskyBiscuts - What is the formula for that Median column or is there one?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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])

Highlighted
Super User IV
Super User IV

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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.

Highlighted
Super User IV
Super User IV

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@Greg_Deckler you are fantastic! That was it! It gave me 41 as it should! You've made my day, thank you.

Highlighted
Super User IV
Super User IV

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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! 🙂


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

@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...)

Highlighted

Re: "Expressions that yield variant data-type..." error when your Col. is of "Whole n

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.

 

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors