Forum Discussion

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
8 years ago

Create a table with distinct rows from three other tables

This is probably easy for somebody out there as I think I am just missing something small. I am trying to create a "Link" table from multiple columns in three other tables. However, I am getting some duplicate rows - all fields are exactly the same. I want all duplicate rows removed. So I can use this as a Link or Bridge table to connect three fact tables and another geographic table. I could cut this down to only two columns, one for each key, but I need a minimum of two columns with distinct rows. 

This is what I'm trying:

Link Table =
DISTINCT(UNION(
 SELECTCOLUMNS(
'AOP and Forecast', "%GeoSBUProductLineKey", 'AOP and Forecast'[%GeoSBUProductLineKey], "%GeoKey", 'AOP and Forecast'[%GeoKey], "Region1", 'AOP and Forecast'[Region - 1st Level], "Region2", 'AOP and Forecast'[2nd Level], "Region3", 'AOP and Forecast'[3rd Level], "Region4", 'AOP and Forecast'[4th Level], "SBU", 'AOP and Forecast'[SBU ], "Product Line", 'AOP and Forecast'[Product Line ]
),
 SELECTCOLUMNS(
'CopaEndCustomer2', "%GeoSBUProductLineKey", CopaEndCustomer2[%GeoSBUProductLineKey], "%GeoKey", 'CopaEndCustomer2'[%GeoKey],"Region1", 'CopaEndCustomer2'[World Region WILL CHANGE], "Region2", 'CopaEndCustomer2'[Geo Level2 WILL CHANGE], "Region3", 'CopaEndCustomer2'[Geo Level3 WILL CHANGE], "Region4", 'CopaEndCustomer2'[Geo Level4 WILL CHANGE], "SBU", 'CopaEndCustomer2'[SBU], "Product Line", 'CopaEndCustomer2'[ProfitCenterGroup]
),
    SELECTCOLUMNS(
'OrdersView', "%GeoSBUProductLineKey", OrdersView[%GeoSBUProductLineKey], "%GeoKey", 'OrdersView'[%GeoKey],"Region1", 'OrdersView'[World Region WILL CHANGE], "Region2", 'OrdersView'[Geo Level2 WILL CHANGE], "Region3", 'OrdersView'[Geo Level3 WILL CHANGE], "Region4", 'OrdersView'[Geo Level4 WILL CHANGE], "SBU", 'OrdersView'[SBU], "Product Line", 'OrdersView'[ProfitCenterGroup]
)
))

ImkeF Do you have any ideas?

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Shelley,


    Can you please share some sample data to help us clarify your scenario?

    BTW, I don't think distinct function will suitable for your requirement, current it will compare with all columns and remove duplicate records.

     

    Regards,

    Xiaoxin Sheng