cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RiskyBiscuts
Advocate I
Advocate I

"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

@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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

15 REPLIES 15
RiskyBiscuts
Advocate I
Advocate I

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

@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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Maestro!

@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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

 

🙂


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

@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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors