cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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

@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
        )
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.

View solution in original post

7 REPLIES 7
Super User II
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

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

 

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 

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

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? 

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

@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
        )
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.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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