Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX measure for Unit Conversion

Please I need help. I have a field named Size and example of information in it is (e.g 30 GB, 50 MB, 40 TB).   My goal is to create another column with DAX by converting all the values into GB. So if a value is 30 GB, no calculation will be done, if a value is 50 MB, the condition will divide the 50 by 1000, if a value is 40 TB, the condition will divide 40 by 0.001. 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    DAX measure for Unit Conversion1 =
SWITCH(
TRUE(),
RIGHT('Table'[Size],2)="GB",VALUE( LEFT('Table'[Size],LEN([Size])-(SEARCH("GB",[Size])-1))),
RIGHT('Table'[Size],2)="MB",DIVIDE( VALUE( LEFT('Table'[Size],LEN([Size])-(SEARCH("MB",[Size])-1))),1000),
RIGHT('Table'[Size],2)="TB",VALUE( LEFT('Table'[Size],LEN([Size]) -(SEARCH("TB",[Size])-1)))/0.001)

    2. Select [DAX measure for Unit Conversion1] – set the decimal point to 2.

    3. Result:

     

    Best Regards,

    Liu Yang

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

3 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

     

     

    Here is a link to download a sample solution file:
    DAX measure for Unit Conversion 2022-08-10.pbix

    GB Value = 
SWITCH(
    TRUE(),
    CONTAINSSTRING('Table'[Column1], "GB"), 'Table'[Column1],
    CONTAINSSTRING('Table'[Column1], "MB"), VALUE(LEFT('Table'[Column1], FIND(" ", 'Table'[Column1], 1) - 1 )) / 1000 & " MB",
    CONTAINSSTRING('Table'[Column1], "TB"), VALUE(LEFT('Table'[Column1], FIND(" ", 'Table'[Column1], 1) - 1 )) / 0.001 & " TB"
)

     

     


    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.


    Showcase Report – Contoso By SpartaBI

    SpartaBI Logo

    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Oh wow, thank you so much. This really helps. But is there a way to make usre the units does not show again in the converted column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    DAX measure for Unit Conversion1 =
SWITCH(
TRUE(),
RIGHT('Table'[Size],2)="GB",VALUE( LEFT('Table'[Size],LEN([Size])-(SEARCH("GB",[Size])-1))),
RIGHT('Table'[Size],2)="MB",DIVIDE( VALUE( LEFT('Table'[Size],LEN([Size])-(SEARCH("MB",[Size])-1))),1000),
RIGHT('Table'[Size],2)="TB",VALUE( LEFT('Table'[Size],LEN([Size]) -(SEARCH("TB",[Size])-1)))/0.001)

    2. Select [DAX measure for Unit Conversion1] – set the decimal point to 2.

    3. Result:

     

    Best Regards,

    Liu Yang

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