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

[RiskyBiscuts]

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. 

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?

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,

[Greg_Deckler · Accepted solution]

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

[RiskyBiscuts]

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

[Greg_Deckler]

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

[marcorusso · Accepted solution]

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.

[Rikirk]

Maestro!

[RiskyBiscuts]

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

[Greg_Deckler]

@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

[marcorusso]

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.

[Greg_Deckler]

@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

🙂

[RiskyBiscuts]

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

[RiskyBiscuts]

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

[Greg_Deckler · Accepted solution]

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

[RiskyBiscuts]

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

[Greg_Deckler]

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

[RiskyBiscuts]

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

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