Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

🙂


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors