Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

Convert units

 

Hi,

 

Column A:C are length, width and Height and column D is unit of dimension of each rows, column D contain multiple unit of dimension which is CM, MM and M.

 

How can I convert CM and MM into M based on the unit of dimesion column D? In Excel I am apply the convert function but the same function in BI completly is difference. 

 

I would like to achieve my desired result by using new calculate column.

 

Can you please advise. 

 

Rules:

 

1.If column D is CM and MM then convert as Meter (M) from E:G

 

2.If column D is M then return as same from E:G

 

3.If column D is Blank and Length, Width and Height is 0 then return the same thing from E:G

 

DataResult in Meter (M)
LengthWidthHeightUOM DimensionLength in MWidth in MHeight in M
102030CM0.10.20.3
657146CM0.650.710.46
100200300MM123
550110870MM5.51.18.7
12814.5M12814.5
184.818.5M184.818.5
000 000
000 000
000 000

 

 

 

 

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Saxon10 ,

     

    for each of the 3 columns (Lenght, Width and Height) add a calculated column:

    Length in Meter =
SWITCH(
    MyTable[UOM Dimension],
    "M", MyTable[Length],
    "CM", MyTable[Length] / 100,
    "MM", MyTable[Length] / 1000
)

     

    Same formula for the other 2 measures 😉 

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Saxon10 ,

     

    for each of the 3 columns (Lenght, Width and Height) add a calculated column:

    Length in Meter =
SWITCH(
    MyTable[UOM Dimension],
    "M", MyTable[Length],
    "CM", MyTable[Length] / 100,
    "MM", MyTable[Length] / 1000
)

     

    Same formula for the other 2 measures 😉 

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your qucik reply. I am receving error here Exprssions that yield variant data-type cannot be used to define calulated column. 

       

      Those three columns came from as a calculate column and those three column are stored as a text so can you please help me.