Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahra-in
Helper II
Helper II

multiply when a match between two tables

Hi

 

I have 

 

Table 1

 

NameYearSubjectWeightage
Alan2016CCA0.1
Donal2016PPP0.2
Dhoni2017SCA0.15
Ram2018SSS0.12
Siva2018SSS0.18

 

Table 2

NameYearSpend
Alan2016154
Donal2017125
Dhoni2017676
Ram2017457
Donal2016458
Ram2018

784

 

When there is a match between table 1 & 2 row wise, for eg: Alan 2016 in the 1st row of Table 1 match with Alan 2016 of Table 2 1st row. if matched the I want a calculated column in Table 2 by multiplying Spend with weightage

 

The result shall be in Table 2 as

 

NameYearSpendWeighted Spend 
Alan201615415.4
Donal2017125 0
Dhoni2017676101.4
Ram2017457 0
Donal201645891.6
Ram201878494.08

 

Please help & thanks

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @mahra-in

 

Please add the following calculated column to your 'Table 2'

 

Weighted Spend = 
    'Table 2'[Spend] * 
     CALCULATE(
        MIN('Table 1'[Weightage]),
        FILTER(
            'Table 1',
            'Table 1'[Name] = EARLIER('Table 2'[Name]) &&
            'Table 1'[Year] = EARLIER('Table 2'[Year]) 
        )
        )

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

Hi @mahra-in

 

Please add the following calculated column to your 'Table 2'

 

Weighted Spend = 
    'Table 2'[Spend] * 
     CALCULATE(
        MIN('Table 1'[Weightage]),
        FILTER(
            'Table 1',
            'Table 1'[Name] = EARLIER('Table 2'[Name]) &&
            'Table 1'[Year] = EARLIER('Table 2'[Year]) 
        )
        )

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

thank u

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.