I need to put together a deals dashboard to help sales activity. The purpose of this is to match customer requests with supplier offers, so show if there is a business opportunity. The problem is that customers and suppliers usually have several different identifiers for the same product. Like this:
Supplier offers: enter image description here
Customer requests:enter image description here
In order for the offers and requests to match, I need to create a data table with the unique products with the product name and ID we want to use. Next to the Main IDs, I need to enter all the alternative IDs that I met in offers and requests. Something like this:
To be able to make relationship between these tables, for both I added a calculated column at the end of both the offers and requests tables with the following DAX formula:
Main ID = LOOKUPVALUE(product_data[Main ID],product_data[Main ID],'supplier offers'[ID])&LOOKUPVALUE(product_data[Main ID],product_data[ALT ID 1], 'supplier offers'[ID])&LOOKUPVALUE(product_data[Main ID],product_data[ALT ID 2], 'supplier offers'[ID])&LOOKUPVALUE(product_data[Main ID],product_data[ALT ID 3], 'supplier offers'[ID])&LOOKUPVALUE(product_data[Main ID],product_data[ALT ID 4], 'supplier offers'[ID])&LOOKUPVALUE(product_data[Main ID],product_data[ALT ID 5], 'supplier offers'[ID])
So it found the main IDs for all offers and request like this: enter image description here
But after, when I wanted to make relationship between supplier offers and product_data by linking to main identifiers to each other, Power BI shows "Circular dependency was detected: supplier offers [Main ID]" (and the same with customer requests.)
I want to make relationship in order to use the Product Names and Main IDs from the product_data in my visuals.
What should I do to avoid this error? Is there an other solution to lookup values from multiple columns?
Thanks in advance!