Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tamás Héjja]

Dear Experts! 🙂

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:

ID	Product Name	Supplier Name	Price
AAAAA	Product A	Supp1	10
aa_aaa	Product – A	Supp3	12
aaa/AA	Product_A	Supp2	11
BBBBB	Product B	Supp3	18
bbbbb	product b	Supp1	17
BB_bbb	Product_B	Supp2	19

 

Customer requests

ID	Product Name	Customer Name	Price
aaaaa	Product a	Customer1	9
AA_AAA	PRODUCT “A”	Customer2	8
AaAAA	Pro. A	Customer 3	7
BB_BBB	Product – B	Customer2	14
bbb/BB	product “B”	Customer3	16
BBBB_b	“b” product	Customer1	15

 

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:

 

Product_data

Product Name	Main ID	ALT ID 1	ALT ID 2	ALT ID 3	ALT ID 4	ALT ID 5
Product A	AAAAA	aaaaa	AA_AAA	aa_aaa	aaa/AA	AaAAA
Product B	BBBBB	bbbbb	BB_bbb	BB_BBB	bbb/BB	BBBB_b

 

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:

ID	Product Name	Supplier Name	Price	Main ID
AAAAA	Product A	Supp1	10	AAAAA
aa_aaa	Product – A	Supp3	12	AAAAA
aaa/AA	Product_A	Supp2	11	AAAAA
BBBBB	Product B	Supp3	18	BBBBB
bbbbb	product b	Supp1	17	BBBBB
BB_bbb	Product_B	Supp2	19	BBBBB

 

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! 

——————————
Tamás Héjja
——————————

[Shungu Dhlamini]

This sounds like you need to take a step back and work on your data modeling.

——————————
Shungu Dhlamini
Power BI Consultant
shungud@gmail.com
——————————
——————————————-