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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

0

BUG: DAX expression results in variant data type

I was trying to create a DAX expression and despite the data all being formatted correctly and no blanks etc. I was constantly getting this error message:
"The DAX expression for calculated table 'New Table' results in a variant data type for column 'Column A'. Please modify the calculation such that the column has a consistent data type."

Someone assisted me in resolving the issue but it had to be done as a work around because this bug kept causing issues when there was no problem with the data itself.

Status: Delivered

Hi @LCTurner 

After making changes to your DAX, there are no more corresponding errors. Since there is no further error information from your side, I will close the process of this thread. If you still have questions about this post, you can update it.

 

Best Regards,
Community Support Team _ Ailsa Tao

Comments
v-yetao1-msft
Community Support
Status changed to: Needs Info

Hi  @LCTurner 

Can you provide your source data, and your DAX expression? Also, what version of Desktop are you currently using?

 

Best Regards,
Community Support Team _ Ailsa Tao

LCTurner
New Member

Here is a sample of my data

 

UNIQ_IDORGDATA_CPY_NAMEPOS_CODEEMP_COST
ID1Company AJob 1184975
ID2Company BJob 2171233
ID3Company BJob 2183293
ID4Company BJob 292473
ID5Company CJob 2229272
ID6Company DJob 2148892
ID7Company CJob 2204673
ID8Company BJob 2182699
ID9Company BJob 2116287
ID10Company EJob 2176553
ID11Company EJob 2239427
ID12Company CJob 3168012
ID13Company CJob 3167761
ID14Company DJob 4137955
ID15Company BJob 5245209
ID16Company EJob 6234693
ID17Company FJob 7222599
ID18Company BJob 8112344
ID19Company BJob 8113580
ID20Company BJob 8139393
ID21Company EJob 8107764
ID22Company GJob 9199456
ID23Company GJob 9130194
ID24Company FJob 978545
ID25Company FJob 9217730
ID26Company HJob 9186181
ID27Company HJob 989719
ID28Company HJob 9225200
ID29Company HJob 9123443
ID30Company HJob 995538

 

 

I tried multiple variations and ran into the same issue every time.

 

Table_CompaRatio =
VAR SummaryTable =
SUMMARIZE (
'POSDATA',
'POSDATA'[POS_CODE],
"DistinctCount_ORGDATA_CPY_NAME", DISTINCTCOUNT('POSDATA'[ORGDATA_CPY_NAME]),
"Count_UNIQ_ID", COUNT('POSDATA'[UNIQ_ID]),
"Median_EMP_COST", MEDIANX (
FILTER (
ALL('POSDATA'),
'POSDATA'[POS_CODE] = EARLIER('POSDATA'[POS_CODE]) &&
DISTINCTCOUNT('POSDATA'[ORGDATA_CPY_NAME]) > 2 &&
COUNT('POSDATA'[UNIQ_ID]) > 3
),
'POSDATA'[EMP_COST]
)
)
RETURN
CALCULATETABLE (
SELECTCOLUMNS (
SummaryTable,
"POS_CODE", 'POSDATA'[POS_CODE],
"DistinctCount_ORGDATA_CPY_NAME", [DistinctCount_ORGDATA_CPY_NAME],
"Count_UNIQ_ID", [Count_UNIQ_ID],
"Median_EMP_COST", [Median_EMP_COST]
),
ALLEXCEPT('POSDATA', 'POSDATA'[POS_CODE])
)


Which brought up this error message:
"The DAX expression for calculated table 'Table_CompaRatio' results in a variant data type for column 'Median_EMP_COST'. Please modify the calculation such that the column has a consistent data type."


I have ensured that there are no blanks and all EMP_COST values are numeric.

 

I'm using Version: 2.126.1261.0 64-bit (February 2024)

v-yetao1-msft
Community Support
Status changed to: Investigating

Hi @LCTurner 

Please change the expression in your DAX: 

change 


'POSDATA'[EMP_COST]

as

VALUE('POSDATA'[EMP_COST])

vyetao1msft_0-1714963769751.png

Best Regards,
Community Support Team _ Ailsa Tao

v-yetao1-msft
Community Support

Hi  @LCTurner 

Does the above modification solve your problem?

 

Best Regards,
Community Support Team _ Ailsa Tao

v-yetao1-msft
Community Support
Status changed to: Delivered

Hi @LCTurner 

After making changes to your DAX, there are no more corresponding errors. Since there is no further error information from your side, I will close the process of this thread. If you still have questions about this post, you can update it.

 

Best Regards,
Community Support Team _ Ailsa Tao