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

     

pi_eye's avatar
pi_eye
Icon for Resolver IV rankResolver IV
3 years ago

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