cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
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
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

Super User

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```

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.