Forum Discussion
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 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.
Thanks for your attention and appreciate any help given.
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
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
5 Replies
- danextian
Super User
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
Thanks a lot for your help
pi_eyeResolver IV
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
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
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.
