Forum Discussion
Expressions that yield variant data-type cannot be used (multiply with constant value)
Anonymous
Make sure the datatypes are setup correctly in the power query editor. (QTY as wholenumber)
With your test data the following measure worked for me:
Column = IF ( All_Data_View[Shipping Site] = "XYZ", All_Data_View[Part Qty] * 15.2, IF ( All_Data_View[Shipping Site] = "ABC" && All_Data_View[Part Qty] < 100, All_Data_View[Part Qty] * 10, 50 ) )Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
Anonymous
If this is a measure this is not gonna work regardless. The IF statement is not iterating the table and you should use a filter.
From what I can tell this code should result in the same output:
Measure =
CALCULATE (
SUM ( All_Data_View[part_qty] ) * 12,
All_Data_View[supplier_name] = "ABC"
|| All_Data_View[supplier_name] = "XYZ"
)
Hope this helps.
Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
Hi,
Thanks for your reply!
I am trying to calculate transaction fee in single new column using "nested if" for set of data value, and this among one condition.
If there any other way to get this condition worked along with others in same column.
Regards,
CS
Can you share sample data and sample output. Mark me @
Hi Amitchandak,
Here is the sample data.
And i am looking at output as:
If shipping site=XYZ, then (Part Qty)*15.12
else if shipping site=ABC and (Part Qty)<100, then (Part Qty)*10 else 50
Claim N° Claim status Part N° Part description Part Qty Extended Item value Shipping Site Transaction_Fee 1 CLOSED 836308 SPS-KYBD BL W/PT STK 14-FR 25 733.5 XYZ 237.5 2 CLOSED 826630 SPS-KEYBOARD BACKLIT W/POINT STICK FR 20 583 XYZ 190 3 CLOSED 929985 TOP COVER, W/KB PRVCY EURO 2 216.5 ABC 19 4 CLOSED L17971 SPS-KYBD SR BL 15W GR 1 29.02 ABC 9.5 5 CLOSED 922749 KB, BASE TR CP BL CLLB SWIS2 2 169.32 ABC 19 6 CLOSED 934597 Assy, Stand, Yoshi 1 0.01 XYZ 9.5 7 CLOSED 901771 P/S,SFF,180W ENTL17,85% EFF HV,12V 1OT 20 293.2 XYZ 190 8 CLOSED 848311 SPS-KYBD TP BL BEL 30 897.3 ABC 285 9 CLOSED 929985 TOP COVER, W/KB PRVCY EURO 1 108.25 XYZ 9.5 10 CLOSED L15540 SPS-KYBD SR BL 14W EUROA5 1 27.04 XYZ 9.5
Anonymous
I'm still not a 100% sure if you are doing this as a measure or as a calculated column.
In case of a measure try and nest the if statement in the sumx like this:
SUMX ( All_Data_View, IF ( All_Data_View[supplier_name]="ABC" || All_Data_View[supplier_name]="XYZ", All_Data_View[part_qty] *12, <alternative> ) )Incase of a column it will always iterate and should work like this:
IF ( All_Data_View[supplier_name]="ABC" || All_Data_View[supplier_name]="XYZ", All_Data_View[part_qty] *12, <alternative> )Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.Hi Joren, Thanks!
I am doing this in new column, and still get this error "Expressions that yield variant data-type cannot be used to define calculated columns"
Regards,
CS
Anonymous
Make sure the datatypes are setup correctly in the power query editor. (QTY as wholenumber)
With your test data the following measure worked for me:
Column = IF ( All_Data_View[Shipping Site] = "XYZ", All_Data_View[Part Qty] * 15.2, IF ( All_Data_View[Shipping Site] = "ABC" && All_Data_View[Part Qty] < 100, All_Data_View[Part Qty] * 10, 50 ) )Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
