cancel
Showing results for
Did you mean:
Helper I

## How to create a calculated measure that references 2 tables

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

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
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User II

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

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

```RatesCustomerId    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)
PopCustomerId    PrdId   MonthSeqId     Amount 1             1       1              50001             2       1              3002             1       1              7000```

New Member

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

Helper I

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

New Member

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?

Helper I

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

Community Support

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
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors