Forum Discussion

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
23 days ago
Solved

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
  • ShivekMaharaj's avatar
    ShivekMaharaj
    21 days ago

    Hi Vivek26​,

    I would first be careful about the grain before deciding whether to use a calculated column or measure.

    Your two tables are not at the same grain:

    Table 1
-> Order_ID + Item_ID

Table 2
-> Order_ID + Item_ID + Promo_ID

    So for ORD-100 / ITEM-A, Table 1 has one row but Table 2 has two rows because two promotions were applied.

    If you bring Quantity and Unit_Price directly into every promotion row, the original line amount will be repeated for each Promo_ID. That may look correct row by row, but totals can easily double count the base amount.

    For 20M+ rows, I would therefore avoid materializing that calculation as a Power BI calculated column.

    My preferred design would be to create a common OrderItem key, for example:

    Order_ID + Item_ID

    and use that as the shared business grain.

    Microsoft’s many-to-many relationship guidance recommends avoiding direct fact-to-fact relationships where possible and instead using a shared dimension/bridge with one-to-many relationships.

    Conceptually:

    OrderItem
-> Table 1

OrderItem
-> Table 2

    Then calculate measures separately.

    For example:

    Base Amount
= SUMX ( Table1, Table1[Quantity] * Table1[Unit_Price] )

Total Discount
= SUM ( Table2[Discount_Amount] )

Final Amount
= [Base Amount] - [Total Discount]

    If you need one physical row per Order_ID + Item_ID with the final amount, then I would aggregate Table 2 to that grain upstream first:

    Order_ID
Item_ID
Total_Discount

    and join it to Table 1 before loading the semantic model.

    That is usually much more scalable than creating millions of repeated calculated-column values inside Power BI.

    One other consideration: if the two tables remain DirectQuery against different databases, the relationship becomes a cross-source relationship in a composite model. Microsoft documents that these are limited relationships and can have different behavior and performance characteristics.

    So at this volume, if possible, I would bring the required columns into a common analytical layer first, aggregate the promotion table to Order + Item grain, and then model it in Power BI.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

8 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    23 days 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.

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    16 days ago

    Hi Vivek26​,

    We wanted to kindly follow up to check if the solution provided by the users for the issue worked? or let us know if you need any further assistance.

    Thanks and regards,
    Anjan Kumar Chippa

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    23 days 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
    23 days 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

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Community Champion rankCommunity Champion
    21 days ago

    Hi Vivek26​,

    I would first be careful about the grain before deciding whether to use a calculated column or measure.

    Your two tables are not at the same grain:

    Table 1
-> Order_ID + Item_ID

Table 2
-> Order_ID + Item_ID + Promo_ID

    So for ORD-100 / ITEM-A, Table 1 has one row but Table 2 has two rows because two promotions were applied.

    If you bring Quantity and Unit_Price directly into every promotion row, the original line amount will be repeated for each Promo_ID. That may look correct row by row, but totals can easily double count the base amount.

    For 20M+ rows, I would therefore avoid materializing that calculation as a Power BI calculated column.

    My preferred design would be to create a common OrderItem key, for example:

    Order_ID + Item_ID

    and use that as the shared business grain.

    Microsoft’s many-to-many relationship guidance recommends avoiding direct fact-to-fact relationships where possible and instead using a shared dimension/bridge with one-to-many relationships.

    Conceptually:

    OrderItem
-> Table 1

OrderItem
-> Table 2

    Then calculate measures separately.

    For example:

    Base Amount
= SUMX ( Table1, Table1[Quantity] * Table1[Unit_Price] )

Total Discount
= SUM ( Table2[Discount_Amount] )

Final Amount
= [Base Amount] - [Total Discount]

    If you need one physical row per Order_ID + Item_ID with the final amount, then I would aggregate Table 2 to that grain upstream first:

    Order_ID
Item_ID
Total_Discount

    and join it to Table 1 before loading the semantic model.

    That is usually much more scalable than creating millions of repeated calculated-column values inside Power BI.

    One other consideration: if the two tables remain DirectQuery against different databases, the relationship becomes a cross-source relationship in a composite model. Microsoft documents that these are limited relationships and can have different behavior and performance characteristics.

    So at this volume, if possible, I would bring the required columns into a common analytical layer first, aggregate the promotion table to Order + Item grain, and then model it in Power BI.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • jubinsoni's avatar
    jubinsoni
    Icon for Solution Sage rankSolution Sage
    21 days ago

    Hi Vivek26, one extra point that is easy to miss with your sample data. Table 2 is at promo grain, so any amount you carry from Table 1 (Quantity x Unit_Price) would repeat on every promo row for the same item, and a SUM of the final amount over Table 2 would overstate the gross for ORD-100 + ITEM-A. Compute the gross at Table 1 grain and only the discount from Table 2, then subtract at the end. Also, since you hit out-of-memory already, build any Order_ID + Item_ID join key in the source query or Power Query instead of as a DAX calculated column, because a 20M row text key column is itself expensive. I have not tested this on your data volume, so please validate against a few orders first. If one of the replies above solved it, please mark it as the solution.