I have two fact tables:
a) FACT_PURCHASE_ORDERS fields: -PO_Number -Pick Ticket Number -PO_Line_Number (po can have many line numbers) -PO_ITEM (PO_number concatenated with PO_Line_Number with dash IE. "10-1")
Example:
| PO_number | PO_Line_Number | PO_Item |
|---|---|---|
| 10 | 1 | 10-1 |
| 10 | 2 | 10-2 |
| 10 | 3 | 10-3 |
b) FACT_PICK_TICKETS fields:
-Pick Ticket Number (these are unique)
I want a table visual with these fields:
-Pick_ticket_number -PO Number -Measures from both tablesSince I can't join the fact tables I have created a dim table DIM_PICK_TICKET with these relationships:
a) DIM_PICK_TICKET 1 to 1 FACT_PICK_TICKETS (on Pick_ticket_number) b) DIM_PICK_TICKET 1 to Many FACT_PURCHASE_ORDERS (on Pick_ticket_number)
In my table visual I can add:
a) DIM_PICK_TICKET[Pick_ticket_number]
b) FACT_PURCHASE_ORDERS[PO Number]
c) but when I try to add a measure from either fact table I get repeating values.
I have tried creating a dim table DIM_PURCHASE_ORDERs with a distinct PO_ITEM field
a) DIM_PURCHASE_ORDERS 1 to Many FACT_PURCHASE_ORDERS (on PO_ITEM)
b) My thought was to add PO_ITEM to FACT_PICK_TICKETS in the backend view
so I can join to DIM_PURCHASE_ORDERS, but that gives me duplicate
Pick_ticket_numbers which I don't want in that table
Any help is appreciated. Every example I can see of multiple fact tables doesn't seem to match my scenario.
*Edit: I need to display a measure from FACT_PICK_TICKET:
"Back Order Qty = SUM(FACT_PICK_TICKET[BACK_ORDER_QTY])"
This is just a quantity number.
I also want to display a date from FACT_PURCHASE_ORDER called RECEIVED_DATE, this is connected to a dim date table called "Date - Received". FACT_PICK_TICKET does not have a connection to this date table.
