Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Expressions that yield variant data-type cannot be used (multiply with constant value)

Hello,   Could you help with DAX for below situation. IF(OR(All_Data_View[supplier_name]="ABC",All_Data_View[supplier_name]="XYZ"), SUMX(All_Data_View,All_Data_View[part_qty])*(12))   Where 12...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.