I have a table that can have multiple different Scenerios to join onto the fact table.
For example I have 4 columns in Table A to link to my fact table;
Office & "Product target" & Insurer &"Business Event"
|Office||Product Target||Insurer||Business Event||Other Field||Other field||Value|
|All Offices||All Products||All Insurers||NB||A||A||10|
|All Offices||All Products||Insurer A||NB||B||B||15|
|All Offices||All Products||Insurer B||NB||C||C||13|
|All Offices||Product A||Insurer A||REN||D||D||12|
|Office A||Product B||Insurer B||NB||E||E||11|
In my fact Table I can force the values to be the below
'All Offices' & 'All Products' & 'All Insurer' & 'Business Event' AS Key to be able to link everything to the top line, using a where clause on Table A. ('All Offices', 'All Products', 'All Insurers' values does not exist in my fact table)
However, how do I then change my key to look at the Insurer Column and not manually set the value to 'All Insurer' (In the table above the second and third line of data). Where Insurer A exists in my fact table the value should be 15 and not the 10 it is currently linked to.
'All Offices' & 'All Products' & Insurer & 'Business Event'
Without the 'Other Fields' being synthetically joined. 'Other Fields' are being used in data model.
Thanks in Advance,