Match on 3 columns and return value/ add row if no match

[Winter_king94]

I am working with two tables that cannot be joined together using the many to many cardinatlity. My two tables (shipped sales & backlog sales) are both joined to a third table (item).

I am trying to pull in 2 columns (2024 Backlog QTY & 2024 Backlog Revenue) from my Backlog Sales table into my Shipped Sales table based on 3 columns matching between the two tables (Bill to Customer, End Customer, Item Num), or 2 columns (Bill to Customer, End Customer) matching between the two tables as the item number isn't always present across both tables. I've tried using the below to create a calculated column but this is summing the values instead of bringing them directly over. It only shows the correct values when I have them set to 'Average', but then this fudges up the total. 

'24 Backlog QTY = CALCULATE('DOV CPU Backlog Report'[2024 Backlog Quantity],

Filter ('DOV CPU Backlog Report', 'DOV CPU Backlog Report'[Customer Name] = 'Oracle KE24 Data'[Bill To Customer Name] && 'DOV CPU Backlog Report'[SALES ORDER LINE ITEMS.PROD_DW_CPC.T_CUSTOMER_D(END_CUSTOMER_SKEY).CUSTOMER_ACCO] = 'Oracle KE24 Data'[End Customer Name] && 'DOV CPU Backlog Report'[Item] = 'Oracle KE24 Data'[Item Num]))

Any and all help is greatly appreciated!

[rajendraongole1]

Hi @Winter_king94 -you have a many-to-many relationship, you can create a bridge table with distinct keys to help relate Table A and Table B.

Table C (Item) should act as a bridge table between Table A (Shipped Sales) and Table B (Backlog Sales).
Ensure that Table C contains the unique values of Item Num (and possibly Bill to Customer and End Customer) that exist in both tables.

For 2024 Backlog QTY you can use below measure:

24 Backlog QTY =
CALCULATE(
SUM('DOV CPU Backlog Report'[2024 Backlog Quantity]),
FILTER(
'DOV CPU Backlog Report',
'DOV CPU Backlog Report'[Customer Name] = 'Oracle KE24 Data'[Bill To Customer Name] &&
'DOV CPU Backlog Report'[End Customer Name] = 'Oracle KE24 Data'[End Customer Name] &&
(ISBLANK('Oracle KE24 Data'[Item Num]) ||
'DOV CPU Backlog Report'[Item] = 'Oracle KE24 Data'[Item Num])
)
)

For 2024 Backlog Revenue use below calculation similar logic

24 Backlog Revenue =
CALCULATE(
SUM('DOV CPU Backlog Report'[2024 Backlog Revenue]),
FILTER(
'DOV CPU Backlog Report',
'DOV CPU Backlog Report'[Customer Name] = 'Oracle KE24 Data'[Bill To Customer Name] &&
'DOV CPU Backlog Report'[End Customer Name] = 'Oracle KE24 Data'[End Customer Name] &&
(ISBLANK('Oracle KE24 Data'[Item Num]) ||
'DOV CPU Backlog Report'[Item] = 'Oracle KE24 Data'[Item Num])
)
)

above measure should pull the correct revenue from Table B based on the matching columns.Set up a one-to-many relationship between the bridge table and both Table A and Table B.If aggregation issues exists, double-check your data relationships and ensure that the fields used for matching (Bill to Customer, End Customer, and Item Num) are correctly formatted and joined.

Hope the above pointers helps.

[Winter_king94]

Hi, thank you for your help! 

I am unfortunately struggling with creating the measure. 

When I add this line: 

FILTER('DOV CPU Backlog Report', 'DOV CPU Backlog Report'[Customer Name] = 'Oracle KE24 Data'.... PBI isn't letting me select 'Oracle KE24 Data'[Bill To Customer Name]. It's only allowing me to select other measures. Can you help me understand why that might be?

Does the relationship from Table A or Table B to the bridge table need to have an active status?

I feel like I am missing something basic, but I have only been using PBI for 2 months so any tips are greatly appreciated!

[Anonymous · Accepted solution]

Hi, @Winter_king94 

Based on your information, I create sample tables:

Backlog Sales

Shipped Sales

Bridge Table (Item)

Then create relationship:

Create new measures, try the following DAX expression:

24 Backlog QTY = 
CALCULATE(
    SUM('Backlog Sales'[2024 Backlog Quantity]),
    FILTER(
        'Backlog Sales',
        'Backlog Sales'[Bill to Customer] = MAX('Shipped Sales'[Bill to Customer]) &&
        'Backlog Sales'[End Customer] = MAX('Shipped Sales'[End Customer]) &&
        (ISBLANK(MAX('Shipped Sales'[Item Num])) || 'Backlog Sales'[Item Num] = MAX('Shipped Sales'[Item Num]))
    )
)

24 Backlog Revenue = 
CALCULATE(
    SUM('Backlog Sales'[2024 Backlog Revenue]),
    FILTER(
        'Backlog Sales',
        'Backlog Sales'[Bill to Customer] = MAX('Shipped Sales'[Bill to Customer]) &&
        'Backlog Sales'[End Customer] = MAX('Shipped Sales'[End Customer]) &&
        (ISBLANK(MAX('Shipped Sales'[Item Num])) || 'Backlog Sales'[Item Num] = MAX('Shipped Sales'[Item Num]))
    )
)

Put Shipped Sales fields in Table visual, and also put these measures in Table viusal, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.