Forum Discussion

Chaithanya89's avatar
Chaithanya89
Frequent Visitor
3 years ago
Solved

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

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 al...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    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's avatar
calculating
Frequent Visitor
3 years ago

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.