Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  lookup from multiple columns & "circular dependency was detected" error

    Posted 14 days ago

    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! :slightly_smiling_face:



    ------------------------------
    Tamás Héjja
    ------------------------------


  • 2.  RE: lookup from multiple columns & "circular dependency was detected" error

    Gold Contributor
    Posted 13 days ago
    This sounds like you need to take a step back and work on your data modeling.

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