measures using data from more than one table - can it be done without a calculated column?

[mpcarpenter]

I'm trying to create a measure which combines 'quantity' data from one table with 'price' data looked up from a product table. I know I can create a calculated column in the sales table....

PriceLookedUp = RELATED('models'[price]) 

and then make a measure 

TotValueSold = SUMX(sales,sales[PriceLookedUp]*sales[Quantity])

which works OK, but can make a measure that does this directly (ie. without the calculated column)?

Apologies if this  is obvious - I'm fairly new to DAX

Michael

[Anonymous · Accepted solution]

Yes you can!  You were pretty much there on your own too!

Try this:

TotValueSold =
SUMX ( sales, sales[Quantity] * RELATED ( 'models'[price] ) )

[mpcarpenter]

thanks - I'd convinced myself I needed something more complex using a CALCULATE function 

[In my original database (of pretend car sales) there was no quantity field (I was assuming the quantity was always 1) and I'd tried...

TotValueSold = SUM ( 'models'[price]  )

which didn't work. What does work, following your suggestion is...

TotValueSold = SUMX ( sales, RELATED ( 'models'[price] ) )

which is interesting as I guess the "SUMX(sales," bit forces it to iterate over the rows in sales, even if no actual fields from the sales tabel are used)

Michael 

[Anonymous]

Yes, SUMX() does in fact iterate over every row of the sales table...it also generates a row context.  In that row context, there is a value that relates back to the the Product table and has a corresponding Product[Price].  That is how you're able to generate the correct answer.

I'm guessing the straight SUM() didn't work because you have other fields from your Sales table that you want to include in your visuals.  The single-direction relationship would ensure that you got weird results if you tried to slice the Sales measure by a column in your Sales table.  The SUMX() measure will solve that problem, and columns from either Product or Sales can be used on your visuals with no problem.

If you want to tune your model and make it run faster, I propose the following changes:

1. Using a merge queries step in your data model, bring the price column into the Sales table.
2. Call that column "SalesAmount"
3. Now you can build a simple measure

TotValueSold= SUM(Sales[SalesAmount])

This measure will run a lot faster than your current SUMX() measure, since it doesn't have to call the Formula Engine on every single row to look up what price it should use for the summing operation.

[mpcarpenter]

Thanks

Michael