Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

IF & AND Statement

RULE

I have a length, width and height in column A,B,C and column D has unit of dimension

I am trying to calculate volume in M3 based on the column D and dimension (length, width and height)

If column D is CM,MM then convert to all of them as a M3.

If column D is M then return as a M3

If column D is 0 or blanks then return as a blanks

In Excel I am applying the following formula in E2 =IF(D3="","",IF(AND(D3="CM"),A3*B3*C3/1000000,IF(AND(D3="MM"),A3*B3*C3/1000000000,IF(AND(D3="M"),A3*B3*C3/1000))))

I am looking for new calculate column option in order to achieve my desired result

 

Length

Width

Height

UOM Dimension

Result - Volume in M3

10

20

30

CM

0.006

65

71

46

CM

0.212

100

200

300

MM

0.006

550

110

870

MM

0.053

39

23

24

CM

0.022

39

23

24

MM

0.000

1200

890

1400

MM

1.495

0

0

0

 

 

0

0

0

 

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Saxon10 

    use the below query as per your excel formula. If condition is good to use. Tweak your calculation as per your requirement. Plesae cehck, The formula doesn't need to devide by 1k if the UOM is "M"

    _dimRes = 
IF(ISBLANK([UOM]),BLANK(),
            IF([UOM] = "CM", [L]*[W]*[H]/1000000,IF([UOM] = "MM", [L]*[W]*[H]/1000000000,IF([UOM] = "M", [L]*[W]*[H]/1000))))

     

6 Replies

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    I am trying to attempting switch function but I am receiving error. The data stored in number format expect unit of dimension.

     

    Volume = SWITCH(VOLUME[UOM Dimension]="MM",VOLUME[Length]*VOLUME[Width]*VOLUME[Height]/1000000000,VOLUME[UOM Dimension]="CM",VOLUME[Length]*VOLUME[Width]*VOLUME[Height]/1000000)

     

     

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    I am trying to attempting IF/AND function but I am receiving error. 

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Saxon10 

    Please correct me if I wrongly understood.

    Can you try the below for your calculated column?

     

    VolumeColumn =
    SWITCH(TRUE(),
    VOLUME[UOM Dimension]="MM",VOLUME[Length]*VOLUME[Width]*VOLUME[Height]/1000000000,
    VOLUME[UOM Dimension]="CM",VOLUME[Length]*VOLUME[Width]*VOLUME[Height]/1000000)
     

    Jihwan Kim

    If this post helps, then please consider accept it as the solution to help the other members find it more quickly.

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      I already tried this option and it will giva wrong results. Example length is 39, width is 23, height is 24 and unit of dimensions is MM then the above-mentioned DAx return 2.1528E-0 but actual output is 0.00021528. I believe maybe this is format issue.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Saxon10 

    use the below query as per your excel formula. If condition is good to use. Tweak your calculation as per your requirement. Plesae cehck, The formula doesn't need to devide by 1k if the UOM is "M"

    _dimRes = 
IF(ISBLANK([UOM]),BLANK(),
            IF([UOM] = "CM", [L]*[W]*[H]/1000000,IF([UOM] = "MM", [L]*[W]*[H]/1000000000,IF([UOM] = "M", [L]*[W]*[H]/1000))))

     

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thank you so much for your help and advice. Your solution is working well.