Fact Table With Multiple Units of Measure

[MiquelDespuig]

Hello Everyone!

I'm just starting to learn data modeling and DAX and I'm going crazy with a measure I want to achieve. I have the Data Model in the screenshot. Where:

• OrdreCompra-Detall (F4311) is a purchase order line item fact table. Where I have these columns for the calculation:
  
  • Quantitat Rebuda (Quantity Received)
  • Quantitat Demanada UoM (Quantity Received Unit of Measure)
• ArticlePlanta (F4102) is my Dimension Product.
• Article (F4101) is a subset of atributes for Dim Product. Where I have the different Units of Measure of the product.
• FactorsConversió (F41002) where the conversion factors for the different units of the product are stored.

Data Model

Every Product has a primary UoM which can be units, kg or liters. My goal is to calculate the quantity received in KG for every line as a measure so we can analyze purchases by KG or LITERS not only in currency format.

For the measure I guess I'll have to use an Iterator, for example SUMX, over the fact table. See my starting point below:

Quantity Received (KG) =
SUMX(
OrdreCompra-Detall (F4311);
'OrdreCompra-Detall (F4311)'[Quantity Received] * CONVERSION FACTOR
)

I have no clue on how to start writing the DAX code to get the CONVERSION FACTOR in my current situation. How can I get the data from the table with the conversion factors in this situation? Is there anything you think I could do to improve the Data Model?

Thank everyone for the help! All inputs appreciated!

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[MiquelDespuig]

@Stachu I've filtered the data for one of the articles which has multiple units of measure. Not all articles have them. Hope this helps!

OrdreCompra-Detall (F4311)

Spoiler

_Document	_CodiLinia	_Adreça	_Data Entrega Sol·licitada	_Data Creació PO	_Data Entrega Promesa	_Data Entrega	_Item	_Article	Quantitat Demanada UoM	Quantitat Demanada	Quantitat Rebuda	Cost Unitari	Quantitat Demanada Article	Pest Total Línia	Pes Total Línia UoM	Volum Total Línia	Volum Total Línia UoM	_UnitatNegoci	_ArticlePlanta_FK	Quantitat Demanada Article UoM
20000033	3000	16184	120023	120007	120023	120023	5087	101098	UN	14	14	187	350	350	KG	14	LT	10VILFR1	10VILFR1:5087	KG
20000838	3000	16184	120085	120071	120085	120084	5087	101098	UN	162	162	187	4050	4050	KG	162	LT	10VILFR1	10VILFR1:5087	KG
20000218	1000	16184	120037	120024	120037		5087	101098	UN	44	0	187	1100	1100	KG	44	LT	10VILFR1	10VILFR1:5087	KG
20001028	1000	16184	120118	120083	120118	0	5087	101098	UN	74	0	187	1850	1850	KG	74	LT	10VILFR1	10VILFR1:5087	KG
20000280	1000	16184	120037	120029	120037	120036	5087	101098	UN	44	44	187	1100	1100	KG	44	LT	10VILFR1	10VILFR1:5087	KG
20000461	1000	16184	120054	120043	120054	120051	5087	101098	UN	25	25	187	625	625	KG	25	LT	10VILFR1	10VILFR1:5087	KG
20000496	1000	16184	120055	120045	120055	120055	5087	101098	UN	28	28	187	700	700	KG	28	LT	10VILFR1	10VILFR1:5087	KG
20000336	1000	16184	120050	120034	120050	120049	5087	101098	UN	37	37	187	925	925	KG	37	LT	10VILFR1	10VILFR1:5087	KG
20000587	1000	16184	120062	120051	120062	120059	5087	101098	UN	28	28	187	700	700	KG	28	LT	10VILFR1	10VILFR1:5087	KG
20000655	1000	16184	120065	120058	120065	120064	5087	101098	UN	56	56	187	1400	1400	KG	56	LT	10VILFR1	10VILFR1:5087	KG
20000760	1000	16184	120078	120065	120078	120077	5087	101098	UN	25	25	187	625	625	KG	25	LT	10VILFR1	10VILFR1:5087	KG

ArticlePlanta (F4102)

Spoiler

_Item

_UnitatNegoci

_Marca

_Eco/Conv

_Tipología

_Subclassificació

_Alérgenos

_Intrastat

Branch/Plant

Eco/Convencional

Tipología

Subclasificación

Alérgeno

_ArticlePlanta_PK

5087

10VILFR1

CON

MP

IBR

FRIGORIFIC

CONVENCIONAL

MATERIA PRIMERA

INGREDIENT

.

10VILFR1:5087

Article (F4101)

Spoiler

_Item	_Article	_Marca	_Marca/Propiedad	UoM Primary	UoM Secondary	UoM Purchasing	UoM Weight	UoM Volume	Descripció Article
5087	101098			KG	UN	UN	KG	LT	GLUCOZYME BIDÓ 25 KG

 FactorsConversió (F41002)

Spoiler

_Item	_FromUoM	_ToUoM	Conversion Factor	To UoM	From UoM
5087	KG	LT	0,04	Liters	Kilograms
5087	KG	UN	0,04	Units	Kilograms

[shawnsoris]

Was there a solution provided for this? I am working with a similar table with From Units & To Unit conversions at an individaul item level. 

[MiquelDespuig]

@shawnsoris a DAX solution was never provided for this situation, and I'm definitely not enough proficient in DAX to provide one.

What I ended up doing:

• It's ok to have a column with differents units of measure in a fact table but be very careful with this column as its not additive (try to have additive columns as much as you can).
• In wanted to provide a lot of functionality by givind the user the ability to select different units of measure dynamically. BUT! Talking to the end user they wanted to analize in 2 specific units of measure. Pallets and Liters.
• So, I joined the conversion factor table to the fact table in Power Query by item and UoM for the transaction and added a Liters column and a Pallets column. Once you have a column entirely in one unit of measure then it becomes additive. Your DAX now is very simple: 

SUM( 'Table Name'[Column In Specific UoM] )​

• It depends a lot in your data source but try to be aware of: What happens if the transaction does not have a conversion factor? It should be zero, or you want to place a really big number to have a flag that something went wrong, or there's a default value that should be used.

In my opinion the good thing about this solution is that all one-to-many relationships end up pointing in the direction you want them to and you can skip loading the conversion factor table to your model so it's simpler and easier to use.

If I wanted to calculate dynamically the conversion factors now I would go with maybe a virtual relationship or propagating a filter. These 3 awesome articles from The Italians helped me a lot:

• How to relate tables in DAX without using relationships - SQLBI
• Physical and Virtual Relationships in DAX - SQLBI
• Propagating filters using TREATAS in DAX - SQLBI

Hope this helps, and if you go the DAX route please share your solution! 😜