topic Re: DAX measure for Unit Conversion in DAX Commands and Tips

DAX measure for Unit Conversion

Anonymous — Wed, 10 Aug 2022 15:29:47 GMT

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.

Re: DAX measure for Unit Conversion

SpartaBI — Wed, 10 Aug 2022 15:44:16 GMT

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

Re: DAX measure for Unit Conversion

Anonymous — Wed, 10 Aug 2022 15:55:24 GMT

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?

Re: DAX measure for Unit Conversion

Anonymous — Wed, 17 Aug 2022 09:46:35 GMT

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