UOM conversion relationship with many to many

[Anonymous]

I have a main fact table and dim_uom table shown below.  Highlighted in yellow below is my desired output showing that the user has the ability to select from a dropdown "alternate_uom" so I am in need of creating the relationship between my fact table and dim_uom table with the issue of many to many.

[v-jayw-msft]

Hi @Anonymous ,

Create a Many to Many relationship and create a measure like below:

Measure = SELECTEDVALUE(fact_table[unit_price])*SELECTEDVALUE(Dim_UOM_Conv[conversion_factor])

Best Regards,

Jay

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

Sumx(Fact , Fact[Unit Price] * maxx(filter(DIM_UOM, DIM_UOM[Product_Num] = fact[Product_num] && DIM_UOM[Batch] = selectedvalue(Batch[Batch])), DIM_UOM[Conversion_factor]))

[Anonymous]

Thanks so much, this worked great.  Can you explain why you need to use maxx for this situation?