DAX measure for Unit Conversion

[Anonymous]

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 · Accepted solution]

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

[SpartaBI]

@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"
)

Showcase Report – Contoso By SpartaBI



      

[Anonymous]

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?