cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Relationship for supplier coverage based on location of customers

Hi,

 

I am hoping someone can help me problem solve this, I am looking for the best way to create a relationship between supplier coverage and customer location.

 

I have a table of Suppliers extracted from our contract database.  One of the elements is coverage, which I want to create a relationship with my other table of Customers based on their location.

 

SUPPLIER NAMECOVERAGE
Supplier ANational
Supplier BNSW
Supplier C

NSW/QLD

 

CUSTOMER NAMESTATE
Customer ANSW
Customer BQLD
Customer C

VIC

 

Unfortunately, due to the format of supplier coverage, I run into two issues;

  1. When a supplier has "national" coverage, no relationship can be made, however I need this to apply to all states.
  2. When a supplier covers multiple states, the format is every state covered separated by slashes ("/").  This is picked up as unique values, not state entities separated by slashes. 

I understand I may need to do some data shaping in power quiery, but I am unsure what I need to do... any ideas please?

 

In the example above, the end result should be;

Supplier A can service all Customers.

Supplier B can only service Customer A.

Supplier C can service Customers A & B.

 

Thank you,

Dan

 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @moonman ,

To create relationships between two tables, you can create a custom column like this in power query and split this column by delimiter:

= Table.AddColumn(#"Changed Type", "New COVERAGE", each if 
[COVERAGE] = "National" then Text.Combine(#"CUSTOMER"[STATE],"/"
) else [COVERAGE])

custom column.pngsplit column.png

Then create relationship between the new column with the [state] column:

relationship.png

re.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @moonman ,

To create relationships between two tables, you can create a custom column like this in power query and split this column by delimiter:

= Table.AddColumn(#"Changed Type", "New COVERAGE", each if 
[COVERAGE] = "National" then Text.Combine(#"CUSTOMER"[STATE],"/"
) else [COVERAGE])

custom column.pngsplit column.png

Then create relationship between the new column with the [state] column:

relationship.png

re.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User III
Super User III

@moonman See attached, I have given both the options, with relation and without relation.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User III
Super User III

@moonman you don't need to do any transformation or don't' need a relationship between these tables, add the following measure and you will get the result

 

Supplier Customer = 
VAR __seperatorforCustomer = ","
VAR __coverageRaw = SUBSTITUTE ( MAX ( Supplier[COVERAGE] ), "/", "|" )
VAR __coverageRawTable = GENERATESERIES ( 1, PATHLENGTH ( __coverageRaw ) ) 
VAR __coverageTable = GENERATE ( __coverageRawTable, ROW ( "@Coverage", PATHITEM ( __coverageRaw, [Value], 0 ) ) )
VAR __coverages = SELECTCOLUMNS ( __coverageTable, "@Coverage", [@Coverage] )
VAR __coverageCustomers =  CALCULATETABLE ( VALUES ( Customer[CUSTOMER NAME] ), TREATAS ( __coverages, Customer[STATE] ) )
RETURN
IF ( MAX ( Supplier[COVERAGE] ) = "NATIONAL",
    CONCATENATEX ( 
        VALUES ( Customer[CUSTOMER NAME] ), 
        Customer[CUSTOMER NAME], 
        __seperatorforCustomer
    ), ---or change this line to "All customers"
    CONCATENATEX (
        __coverageCustomers,
        [CUSTOMER NAME],
        __seperatorforCustomer
    )
)

 

and here is the output:

 

parry2k_0-1610419530664.png

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thanks for the suggestion @parry2k ,

 

Ideally I would like to create a relationship so that when looking at customers for suppliers, customers outside the coverage will automatically be excluded, and vice-versa for suppliers used by customers.

 

The measure works for this one solution, however does not work when it comes to filtering, which is why I need the relationship setup.

 

Cheers,

Daniel

Super User III
Super User III

Hi,

Create a 2 column by 2 rows table (the first is the header row) with only National appearing in the second cell of the first column.  In the right hand side cell, list all states seperated by /.  Using the Query Editor, add the second column of the new table to the first table.  So in the third column of the first table, against National, you will have / seperated States in a single cell.  In the Query Editor, populate all blank cells in the third column of the first table with the entries shown in the second column.  Split the third column by rows using / as the seperator. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

Thank you for your quick response!

 

Is it possible for you to please show an example of what this linking new table would look like?  I am having trouble understanding your solution.

 

Thanks,

Daniel

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors