BUG: DAX expression results in variant data type

[LCTurner]

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.

[v-yetao1-msft]

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]

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

[v-yetao1-msft]

Hi @LCTurner 

Please change the expression in your DAX: 

change 

'POSDATA'[EMP_COST]

as

VALUE('POSDATA'[EMP_COST])

Best Regards,
Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi  @LCTurner 

Does the above modification solve your problem?

Best Regards,
Community Support Team _ Ailsa Tao

[v-yetao1-msft]

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