Create separate table from 3 tables

[Power_It_Up]

Hi. 

I have 3 tables which all have a one-to-many relationship. I want to be able to create another table based of different fields from the others.

I've tried many different ways including using the RELATED() function, but the intelsense never gives me any fields to choose from.

Can someone please point me in the right direction? 

Many thanks. 

[Power_It_Up]

@v-tejrama @Hakuna_matata Thank you both very much for your detailed responses. These helped me in resolving my issue. Regards.

[v-tejrama]

Hi @Power_It_Up,

I created new columns in the OrderDetails table for Quantity and UnitPrice, and then added a calculated column to get the total value per row using this DAX:

TotalAmount = OrderDetails[Quantity] * OrderDetails[UnitPrice]

After that, I created a new table using DAX to include related customer and order information along with the calculated total amount:  

CustomerOrderDetails =

SELECTCOLUMNS (

    ADDCOLUMNS (

        OrderDetails,

        "CustomerName", RELATED(Customers[CustomerName]),

        "OrderDate", RELATED(Orders[OrderDate])

    ),

    "CustomerName", [CustomerName],

    "OrderDate", [OrderDate],

    "ProductName", OrderDetails[ProductName]

)

 
Find attached .PBIX file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.

[v-tejrama]

Hi @Power_It_Up ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.

[Hakuna_matata · Accepted solution]

Hi @Power_It_Up , There are many reasons why the Related() might not be working but a general example to using the same with your problem statement will be:

Let’s say you have these three tables:

Customer (one)

Order (many, related to Customer)

Order detail (many, related to Order)

You want a new table with:
CustomerName, OrderDate, ProductName

Here’s how you might do it:

NewTable =
SELECTCOLUMNS (
ADDCOLUMNS (
OrderDetails,
"CustomerName", RELATED (Customers[CustomerName]),
"OrderDate", RELATED (Orders[OrderDate]),
"ProductName", OrderDetails[ProductName]
),
"CustomerName", [CustomerName],
"OrderDate", [OrderDate],
"ProductName", [ProductName]
)

Double-check your relationships in the model:

Customer[ID]→Order[CustomerID]

Order[ID]→OrderDetails[OrderID]

Also, Making sure you're using Related() in a table that is on the many side of the relationship (to pull from the one side).

Hope it help! Please mark this as a solution if this is helpful. To better understand please share an example pbix.