Unit of Measurement Conversions Calculated in Power Query or in DAX

[kewaynes]

I two reports that need to do unit of measurement conversions on several fields like weight, speed, volume, etc...

The reports have 4M records and 20M records, respectively. Because we are doing all these calculations in PQ, it of course increases the cost of our refreshes. But I know that is helps with report performance. However, the reports are only using a fraction of the total records (RLS) at any given time, so I'm assuming the report performance increase by doing the calculations in PQ is marginal. 

We are doing some testing to see what the best trade-off is, but is there any best practice guidance on this, especially considering reports that get into the +10M records?

[Sergii24 · Accepted solution]

Hi @kewaynes, usually different units of measure are attributes of item/product. Therefore, they are part of dimensional tables (or bridge tables in case you have multiple units of measure per single item).

Do you have 4 and 20 million of products? I suppose these tables are fact tables, that contain transactional information about your items. If it's so, you can build the following data model:

Then you create a measure in your report, that performs UoM conversion during the report execution.
I hope it helps! 🙂

[kewaynes]

Yep. This makes sense. Doing the conversion in the DAX so that it executes at query time is always what I thought the best practice was but wanted to double-check.