Connecting two different tables for display and calculations

[LanrexTee]

Hi Team,

 Pls I'm new and  struggling with connecting two different tables with no common unique key and displaying desired data accurately to evaluate the difference between the demand and Supply column and do further calculations.

 Aim is to display the Weekly Total  Product demand and supply by Area and ealuate the difference.....also by City

Sample data above.

I made a separate table with a unique key by combining Product name with Week and City code to create a relationship , I also tried creating another table with Products Names only, I got some results however the result was not ok when I tried filtering by Area or Rig which means the relationship wasn't ok.

Will merging work?, but my data is very big and i dont know much about merging yet.

Thanks

Larry

[v-shex-msft]

Hi @LanrexTee,

I'd like to suggest you to add merge column to these tables and create below bridge table('UniqueKey' to 'Unique Key') to link these merge columns:

Column formula:

Unique Key =
[Week] & ","  & [City Code]  & ","  & [Product]

Table formula:

Bridge Table =
ADDCOLUMNS (
    CROSSJOIN (
        CROSSJOIN (
            DISTINCT ( UNION ( VALUES ( Demand[Week] ), VALUES ( Supply[Week] ) ) ),
            DISTINCT (
                UNION ( VALUES ( Demand[City Code] ), VALUES ( Supply[City Code] ) )
            )
        ),
        DISTINCT ( UNION ( VALUES ( Demand[Product] ), VALUES ( Supply[Product] ) ) )
    ),
    "UniqueKey", [Week] & ","
        & [City Code]
        & ","
        & [Product]
)

After these steps, you can use bridge table columns to create visual row and column fields, then use value columns from demand and supply tables as value field.

Regards,

Xiaoxin Sheng

[LanrexTee]

---

@v-shex-msft wrote:

Hi @LanrexTee,

I'd like to suggest you to add merge column to these tables and create below bridge table('UniqueKey' to 'Unique Key') to link these merge columns:

Column formula:

Unique Key =
[Week] & ","  & [City Code]  & ","  & [Product]

Table formula:

Bridge Table =
ADDCOLUMNS (
    CROSSJOIN (
        CROSSJOIN (
            DISTINCT ( UNION ( VALUES ( Demand[Week] ), VALUES ( Supply[Week] ) ) ),
            DISTINCT (
                UNION ( VALUES ( Demand[City Code] ), VALUES ( Supply[City Code] ) )
            )
        ),
        DISTINCT ( UNION ( VALUES ( Demand[Product] ), VALUES ( Supply[Product] ) ) )
    ),
    "UniqueKey", [Week] & ","
        & [City Code]
        & ","
        & [Product]
)

After these steps, you can use bridge table columns to create visual row and column fields, then use value columns from demand and supply tables as value field.

Regards,

Xiaoxin Sheng

---

---

@v-shex-msft wrote:

Hi @LanrexTee,

I'd like to suggest you to add merge column to these tables and create below bridge table('UniqueKey' to 'Unique Key') to link these merge columns:

Column formula:

Unique Key =
[Week] & ","  & [City Code]  & ","  & [Product]

Table formula:

Bridge Table =
ADDCOLUMNS (
    CROSSJOIN (
        CROSSJOIN (
            DISTINCT ( UNION ( VALUES ( Demand[Week] ), VALUES ( Supply[Week] ) ) ),
            DISTINCT (
                UNION ( VALUES ( Demand[City Code] ), VALUES ( Supply[City Code] ) )
            )
        ),
        DISTINCT ( UNION ( VALUES ( Demand[Product] ), VALUES ( Supply[Product] ) ) )
    ),
    "UniqueKey", [Week] & ","
        & [City Code]
        & ","
        & [Product]
)

After these steps, you can use bridge table columns to create visual row and column fields, then use value columns from demand and supply tables as value field.

Regards,

Xiaoxin Sheng

---

Thanks @v-shex-msft for prompt help . Your approach should work as well when I checked. However the issue will  be if I make a slicer to filter by Area and I take the Field value from either of the Demand or Supply Table, some Areas will be missing because not all Areas in one table are in the other table and the bridging table doesn't contain all possible 'Areas'.

What I did, as a beginner. before I read your post is as follows:

- Rename all Columns of both tables to match (Since original data has different names for the columns) except the Demand and Supply columns

-Append both Tables as a new table 'AppendAB'

-Create a calculated column of Unique Key (by joining Week with City Code & Product )

- Remove all duplicates of the 'Unique Key'

 - Hide the Plan & Supply columns in the 'AppendAB' Table (since I removed the duplicates, those two columns will have missing data)

-Connect the AppendAB Table to Demand & Supply Table using the Unique key.

In this case, I will have all possible 'Areas' in 'AppendAB' Table as well.

Many thanks

[v-shex-msft]

Hi @LanrexTee,

I attached the sample file below, you can try it if works for your requirement.

Regards,

Xiaoxin Sheng

[LanrexTee]

---

@v-shex-msft wrote:

Hi @LanrexTee,

I attached the sample file below, you can try it if works for your requirement.

Regards,

Xiaoxin Sheng

---

Thanks @v-shex-msft

Great! Just one thing missing as I said earlier, a slicer with "Area" will be different for each table as attached, Is it possible to include the 'Area' in the Bridge Table as well so that a Slicer to filter by Proucts per Area will have a merged values of all Areas. Pls see the attached below on how I want to have the Areas merged as well.

Thanks and regards.

Lanre

[v-shex-msft]

Hi @LanrexTee,

In fact, I found area field seems based on city code.
Maybe you can add a calculated column on bridge table to lookup related area form these tables.

Update: Add sample formula.

Area =
VAR dict =
    DISTINCT (
        UNION (
            ALL ( Demand[City Code], Demand[AREA] ),
            ALL ( Supply[City Code], Supply[AREA] )
        )
    )
RETURN
    CONCATENATEX (
        FILTER ( dict, [City Code] = EARLIER ( 'Bridge Table'[City Code] ) ),
        [AREA],
        ","
    )

Regards,

Xiaoxin Sheng