Forum Discussion

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
1 day ago

Two Large tables assocication

Hi everyone,

I have 2 tables containing 20+ million rows each, coming from 2 different databases.

Table 1: Order + Item grain (one row per unique item within a specific order)

Order_ID | Item_ID | Quantity | Unit_Price
ORD-100 | ITEM-A | 2 | $15.00
ORD-100 | ITEM-B | 1 | $40.00
ORD-101 | ITEM-A | 1 | $15.00
ORD-102 | ITEM-C | 3 | $10.00
ORD-102 | ITEM-D | 1 | $5.00

Table 2: Order + Item + Promo grain (one row per promo applied to a specific item within an order)

Note that ORD-100 + ITEM-A spans two rows here because two different promotions were applied to that exact item.

Order_ID | Item_ID | Promo_ID | Discount_Amount
ORD-100 | ITEM-A | SAVE10 | $1.50
ORD-100 | ITEM-A | FREESHIP | $5.00
ORD-100 | ITEM-B | SAVE10 | $4.00
ORD-102 | ITEM-C | BOGO_HALF | $5.00
ORD-102 | ITEM-C | MEMBER5 | $1.50

Users now want to see the final amount in Table 2.

Because of the large number of rows, when I try to create a calculated column I get an out-of-memory exception. I can’t precalculate and store it in the DB at this point since the tables are in different databases.

What is my best option here?

Need Help

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 day ago

    Hi Vivek26​ - My preferred approach would be to aggregate the promotions and perform the join upstream, if feasible. Since the tables reside in different databases, this could involve staging the required data in a common analytical layer and performing the aggregation and join there. If using Power Query, I would first validate whether query folding is supported across the two sources. If upstream processing is not possible, I would evaluate a measure-based solution or a composite model, depending on the storage modes and reporting requirements.

    Suggest another option as well, you can bring both sources into a common analytical layer and perform the join in SQL or a pipeline. You can then calculate the final amount upstream instead of materializing millions of calculated columns in Power BI.

    Hope this helps.

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    1 day ago

    The best approach would be to make an aggregation table for the Table 2
    You can achieve that in dax with something like 

    PromoAgg =
SUMMARIZE(
    Promotions,
    Promotions[Order_ID],
    Promotions[Item_ID],
    "Discount",
    SUM(Promotions[Discount_Amount])
)

    After that, you can create a calculated column for your calaculation

    If you prefer to achieve that in Power Query

    You can Group your promotion Order ID & Item Id in your table2 and make a sum on the discount amount, so you will have a table with the right granularity and after that you can merge your two tables

  • DaniyalKhaleel1's avatar
    DaniyalKhaleel1
    Icon for Resolver II rankResolver II
    1 day ago

    Best option: create a measure instead of a calculated column, and aggregate discounts at the Order_ID + Item_ID grain.

    Final Amount =

    SUMX(

    'Table 2',

    LOOKUPVALUE(

    'Table 1'[Quantity],

    'Table 1'[Order_ID], 'Table 2'[Order_ID],

    'Table 1'[Item_ID], 'Table 2'[Item_ID]

    )

    * LOOKUPVALUE(

    'Table 1'[Unit_Price],

    'Table 1'[Order_ID], 'Table 2'[Order_ID],

    'Table 1'[Item_ID], 'Table 2'[Item_ID]

    )

    - 'Table 2'[Discount_Amount]

    )

    However, with 20M+ rows in each table, this may still be slow. A better long-term solution is to aggregate Table 2 by Order_ID + Item_ID first, then join it to Table 1 using a composite key or a source-side query.

    If you cannot modify either database, consider Power Query aggregation and merging (if folding is supported) or a composite model with DirectQuery to avoid materializing a massive calculated column