How to create a calculated measure that references 2 tables

[pcavacas]

I have 1 dataset that looks like this

Rates

CustomerId

PrdId

MonthSeqId

Rate

Then I have another dataset that looks like this

POP

CustomerId

PrdId

MonthSeqId

Amount

What I want to do is create a DAX expression that in the Rates dataset that will take the Rate from that table and multiple it by the corresponding Amount in the Pop table (where the CustomerId, PrdId, and MonthSeqId) from the datasets are equal.

I'm new to DAX expression and am having trouble figuring out the correct syntax for this.

[Ruslan66]

I'm not 100% this will work but you can give it a try...

Make sure the relationship between these 2 tables is set by either CustomerID or PrdID

NewMeasure = SUMX (Pop, Pop[Amount] * RELATED(Rates[Rate]) )

Another approach would be to merge the 2 tables using "Append Queries as New" and then add a new calculated column that takes Rate and multiply it by Amount within a single table. but I think the first choice is less hassle 

[pcavacas]

But there is no relationship between the 2 table because it is just a logical relationship, no keys defined between them

[Ruslan66]

You mean that Customer (ID #1) in Rates table is not the same Customer as (ID #1) in Pop table?

Or they are the same customer but you currently dont have a relationship between those 2 tables? 

[pcavacas]

They are the same customer, but there is no relationship and the Relationship is not just Customer, but Customer, Product and Month and Power BI won't let me draw a relationship line between them

[TomMartens]

Hey,

without some sample data and not knowing if the tables you have mentioned I would suggest that you give the LOOKUPVALUE a try: https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax

Hopefully this gives you an idea

Regards Tom

[pcavacas]

The data would be something like below.  The NewCalculatedMeasure column is the one I'm trying to create

Rates
CustomerId    PrdId   MonthSeqId    Rate        NewCalculatedMeasure
1             1       1             5           25000 (5*5000)
1             2       1             3           900   (3*300)
2             1       1             4           2800  (4*7000)

Pop
CustomerId    PrdId   MonthSeqId     Amount 
1             1       1              5000
1             2       1              300
2             1       1              7000

[v-chuncz-msft · Accepted solution]

@pcavacas,

You may refer to the measure below.

Measure =
VAR c =
    SELECTEDVALUE ( Rates[CustomerId] )
VAR p =
    SELECTEDVALUE ( Rates[PrdId] )
VAR m =
    SELECTEDVALUE ( Rates[MonthSeqId] )
VAR r =
    SELECTEDVALUE ( Rates[Rate] )
RETURN
    r
        * CALCULATE (
            SUM ( Pop[Amount] ),
            Pop[CustomerId] = c,
            Pop[PrdId] = p,
            Pop[MonthSeqId] = m
        )