DAX Multiply One to Many

[syncwithconnex]

Hi,

My goal is to multiply the product price column by the license key month subscribed column. I have a one (product) to many (license key) relationship.

Data

Product

SKU: BBB

MonthlyCharge: 100

SKU: ABC

MonthlyCharge: 200

License Key

Email: joe@

SKU: BBB

MonthsSubscribed: 10

Email: jane@

SKU: ABC

MonthsSubscribed: 50

This formula just gets the first price in the product table:

LTV = MIN(JMAWebStoreProduct[MonthlyCharge]) * [MonthsSubscribed]

Is there a way to pull the monthly charge, based on the SKU field? I created the one to many on the SKUs.

[amitchandak · Accepted solution]

@syncwithconnex , depending on relation , you can copy values in following ways

https://www.youtube.com/watch?v=czNHt7UXIe8

[syncwithconnex]

Thanks, but it still fails:

MonthlyFee = LOOKUPVALUE(Product[MonthlyCharge], Product[Sku], LicenseKey[JMAWebStoreSku])

A single value for column 'JMAWebStoreSku' in table 'LicenseKey' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.