Power BI, unit conversion case in direct query

1.4k Views Asked by At

This is a very simple scenario for which i haven't found a solution yet that works with Direct Query. The purpose is to allow the user select the units in which values are displayed.

The unit conversion is just a multiplication by a constant.

There are two tables. The first one called Data with two columns, Date and Value. The second table called Units that have two columns: UnitName and ConversionFactor. The user will select the UnitName in a slicer. The dashboard should present the result of Data[Value] multiplied by the corresponding ConversionFactor.

I wasn't able to create a calculated column that would allow me to do this simple multiplication in Direct Query.

Trying to introduce a Measure in the calculated column would not work and the RELATED function would not work either as I couldn't establish a relationship between both tables.

1

There are 1 best solutions below

0
On BEST ANSWER

This can be solved using a simple calculated measure:

MyMeasure = AVERAGE(Table1[Value]) * AVERAGE([Table2[ConversionFactor]])

When user selects the Table2[UnitName] in the slicer, the visualization of MyMeasure against Table1[Date] updates the converted value correctly in Direct Query.