Convert kg, tons, lbs in new measure with Switch function

[Chaithanya89]

Dear Community,

I have a table which consists of 2 column with weight and respective weight units either KG or LB. And would like get help on DAX to create a new column measure which calculate all the weight in tonnes. 

How can I have the solution in the following forms

1. using switch function to convert lbs and kgs into tonnes

2. use a reference table to get the solution and enable the users on the visual to toggle between tonnes and lbs

Product ID	Base Unit	produced quantity
12345	kg	690
54321	lb	580
67890	lb	450
98765	kg	500
15983	kg	625
38951	kg	548

 THe sample pbix file is saved in the following location.

Pbix sample file 

Thanks for your attention and appreciate any help given. 

[danextian · Accepted solution]

Hi @Chaithanya89 ,

There are several approaches to this problem. You can use SWITCH or field parameters. For the former, you need to create a disconnected table (without relationship to fact table) that will serve as holder of the switch measure. Please see sample DAX formulas below:

Base Unit Table = 
DATATABLE ( "Unit", STRING, "Sort", INTEGER, { { "lbs", 1 }, { "tonnes", 2 } } )

lbs or tonnes = 
SWITCH (
    SELECTEDVALUE ( BaseUnit[Unit] ),
    "lbs", [Quantity (lbs)],
    "tonnes", [Quantity (tonnes)]
)

Please see attached pbix for the details

[calculating]

In Power BI, you can use the SWITCH function to convert between different units of weight, such as kilograms (kg), tons, and pounds (lbs). Here's an example of how you can use the SWITCH function:

Weight (New Measure) =
SWITCH(TRUE(),
'Table'[Unit] = "kg", 'Table'[Weight] * 2.20462, // Convert kg to lbs
'Table'[Unit] = "tons", 'Table'[Weight] * 2204.62, // Convert tons to lbs
'Table'[Unit] = "lbs", 'Table'[Weight], // Leave lbs as it is
BLANK() // Return blank for other units
)

Weight (New Measure) =
SWITCH(TRUE(),
'Table'[Unit] = "kg", 'Table'[Weight] * 2.20462, // Convert kg to lbs
'Table'[Unit] = "tons", 'Table'[Weight] * 2204.62, // Convert tons to lbs
'Table'[Unit] = "lbs", 'Table'[Weight], // Leave lbs as it is
BLANK() // Return blank for other units
)

In the above example, replace 'Table' with the actual name of your table, 'Unit' with the name of the column containing the unit of weight, and 'Weight' with the name of the column containing the weight values.

The SWITCH function checks the value of the 'Unit' column and performs the appropriate conversion based on the condition. It multiplies the 'Weight' column by the conversion factor to convert the weight to the desired unit.

Note that the conversion factors used in the example are approximate values. You may need to adjust them based on your specific conversion requirements.

[Chaithanya89]

Thanks a lot for your help

[pi_eye]

Hi Chaithanya

Another solution could be to create a many to many reference table which you join to the data table

Join using Base Unit to the main table:

If you create a filter based on "preffered unit" and set it to one selected value - this means that for every value of base unit, there is only one corresponding row in the rerference table.

The expression is then: 

Quantity, preferred unit = sumx( Weights,    Weights[produced quantity] / FIRSTNONBLANK(Query1[Divide By],0)  )

The advantage of this is more flexibility, should you wish to add more conversions.

Pi

[pi_eye · Accepted solution]

I'm enable to updload a pbix for some reason - but please see link here to my google drive: https://drive.google.com/file/d/1fF-qCpz3HXZRLTkTlgHyjpr-H14JmuHn/view?usp=sharing

Thank you

[danextian · Accepted solution]

Hi @Chaithanya89 ,

There are several approaches to this problem. You can use SWITCH or field parameters. For the former, you need to create a disconnected table (without relationship to fact table) that will serve as holder of the switch measure. Please see sample DAX formulas below:

Base Unit Table = 
DATATABLE ( "Unit", STRING, "Sort", INTEGER, { { "lbs", 1 }, { "tonnes", 2 } } )

lbs or tonnes = 
SWITCH (
    SELECTEDVALUE ( BaseUnit[Unit] ),
    "lbs", [Quantity (lbs)],
    "tonnes", [Quantity (tonnes)]
)

Please see attached pbix for the details