cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## UOM conversion relationship with many to many

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.

1 ACCEPTED SOLUTION
Super User

@Powerline56 , 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]))

3 REPLIES 3
Community Support

Hi @Powerline56 ,

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@Powerline56 , 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]))

Frequent Visitor

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