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.
5 Comments
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 TaoHere is a sample of my data
UNIQ_ID ORGDATA_CPY_NAME POS_CODE EMP_COST ID1 Company A Job 1 184975 ID2 Company B Job 2 171233 ID3 Company B Job 2 183293 ID4 Company B Job 2 92473 ID5 Company C Job 2 229272 ID6 Company D Job 2 148892 ID7 Company C Job 2 204673 ID8 Company B Job 2 182699 ID9 Company B Job 2 116287 ID10 Company E Job 2 176553 ID11 Company E Job 2 239427 ID12 Company C Job 3 168012 ID13 Company C Job 3 167761 ID14 Company D Job 4 137955 ID15 Company B Job 5 245209 ID16 Company E Job 6 234693 ID17 Company F Job 7 222599 ID18 Company B Job 8 112344 ID19 Company B Job 8 113580 ID20 Company B Job 8 139393 ID21 Company E Job 8 107764 ID22 Company G Job 9 199456 ID23 Company G Job 9 130194 ID24 Company F Job 9 78545 ID25 Company F Job 9 217730 ID26 Company H Job 9 186181 ID27 Company H Job 9 89719 ID28 Company H Job 9 225200 ID29 Company H Job 9 123443 ID30 Company H Job 9 95538 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)
Hi LCTurner
Please change the expression in your DAX:
change
'POSDATA'[EMP_COST]as
VALUE('POSDATA'[EMP_COST])Best Regards,
Community Support Team _ Ailsa TaoHi LCTurner
Does the above modification solve your problem?
Best Regards,
Community Support Team _ Ailsa TaoHi 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
