cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Left Join in calculated table

I have 2 tables which are not joined together now as there is no unique values in either table to join them

Offer- C_No and Ref_No and other feilds

Offer_Discount - C_No and Ref_No and other feilds

I am trying to get the below result in report and stored it in a calculated table.  I bleive I can also add a new column 'Total_amount' in Offer table . Need help of how to acheive this in either ways,

SELECT

DISTINCT R.c_no ,

SUM(ISNULL(r.MONTHLY_AMT,0)) AS SUM_MONTHLY_AMT ,

SUM(ISNULL(rd.DISC_AMT,0) )  AS SUM_DISC_AMT ,

SUM(ISNULL(r.MONTHLY_AMT,0)) - SUM(ISNULL(rd.DISC_AMT,0) )  AS TOTAL_AMOUNT

FROM DBO.Offer R

LEFT JOIN Offer_discount RD

ON RD.c_no = R.c_no

AND RD.ref_no = R.ref_no

GROUP BY R.c_no

2 REPLIES 2
Microsoft

@vijendras

In this scenario, since both tables have multiple entries for same C_No, you have summarize each of them, then you can join them together. You can create calculated tables like below:

Table1 =
SUMMARIZE (
Offer,
Offer[C_No],
Offer[Ref_No],
"SUM_MONTHLY_AMT", SUM ( Offer[MONTHLY_AMT] )
)

Table 2 =
SUMMARIZE (
Offer_Discount,
Offer_Discount[C_No],
Offer_Discount[Ref_No],
"SUM_DISC_AMT", SUM ( Offer_Discount[DISC_AMT] )
)

Then you can use LOOKUPVALUE() function to get the [SUM_DISC_AMT] column from Table 2 to Table 1.

=
LOOKUPVALUE (
Table2[SUM_DISC_AMT],
Table2[C_No], Table1[C_No],
Table2[Ref_No], Table1[Ref_No]
)

Regards,

Frequent Visitor

Thanks. @v-sihou-msftHow can I add a where condition to this ?

similar SQL :

SELECT

DISTINCT R.c_no ,

SUM(ISNULL(r.MONTHLY_AMT,0)) AS SUM_MONTHLY_AMT ,

SUM(ISNULL(rd.DISC_AMT,0) )  AS SUM_DISC_AMT ,

SUM(ISNULL(r.MONTHLY_AMT,0)) - SUM(ISNULL(rd.DISC_AMT,0) )  AS TOTAL_AMOUNT

FROM DBO.Offer R

LEFT JOIN Offer_discount RD

ON RD.c_no = R.c_no

AND RD.ref_no = R.ref_no

where r.status = 1

GROUP BY R.c_no

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors