Skip to main content
cancel
Showing results for 
Search instead 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

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

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

TomMartens
Super User
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

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

 

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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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