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?
