"fact table" as dimension table ?

[Hermes]

Hi there,

I've got standard star schema model, but I need to add to it another dimension table, that store customer product codes (code of sold products recognised by customers in their own systems).

Table has it own unique ID (I've ommited it here), product ID, customer ID and product code itself. 

I want to be able to see codes and use them as filters in pivot table, yet the results of attaching them to star schema is kinda obvious (and bad). 

Could you please suggest to me how to go about modeling so I can use those codes in pivot table field and still get proper values for calculations?

I've attached simpliefied model - https://we.tl/t-jW3vyQAXYG

[amitchandak]

Please find link : https://www.dropbox.com/s/0jgscauytkfr440/starSchema.pbix?dl=0

Done some changes to create the model.

[Hermes]

Thand you very much, but I'm afraid it does not work precisely as intended, because if products for given customer and transaction does not have their own code (as they ofted does), there is no link between fact table and dim's (shown below). I'd like to not use method of creating artificial ID you suggested if possible, i.e. does not use this method plus secondary relationship with USERELATIONSHIP.

[v-chuncz-msft · Accepted solution]

@Hermes 

You may use LOOKUPVALUE to add calculated columns directly.