Forum Discussion
Pesky Ambiguous Paths - Help Needed
Help!
I've attached an image of the current set of relationships, but I need help to try and get this model working correctly.
I'm putting together a company dashboard from data stored in different Sharepoint Lists. I'm not in a superpermissive IT environment, so this is the only real solution I have to store data.
There is a list of unique people in the Company Directors list. Each has a Sharepoint allocated ID number.
There are a list of Companies each with their own SP allocated ID number. HOWEVER, I use a lookup from the Directors list to populate two fields for each company - Director and Authorised Signatory. So if person 1 3 and 5 are company directors of company 11, they will all be associated with that record.
As such i've had to expand that list out as I need visualisations that show the individual name of directors/authorised signatories and allow filtering by person etc etc. Thus the company list in its raw form doesn't have single unique company numbers in it - it has multiple.
I created a bridge for unique company names, taking the data and removing all other fields and removing duplicates so that i have a single list that has 1 record per company.
On top of that, as I need to be able to show where a person is a director, authorised signatory or both, i created another bridge where the role is provided through appending lists together.
This all seems to work fine, but the issue comes when I try to connect the Power of attorney list.
This list has a lookup for both PersonID and CompanyID. I can connect the Directors list on a one to many basis to it, but if I try and connect any company list to it, I get an ambiguous paths error.
I have tried a number of solutions to no avail. I am very much willing to take a different data approach if needs be, but would ideally like a solution where the actual sharepoint lists are left alone.
All help very very much appreciated!!!!
2 Replies
Forgive me, i haven't read all the details but I tried to focus on the error.
"but if I try and connect any company list to it, I get an ambiguous paths error. " Does that mean you are trying to connect 'power of attorney tracker' to 'company bridge' or 'company information'? So you understand why you are getting the 'ambiguous paths' error?
--
So my general advice is to identify what you want to report on and transform your data to create a star schema with unique items in your dimension tables. It looks to me that you are trying to keep the data format of your sharepoint lists and fudging a model to make it work. Am I wrong?
Hey Mr Chilli, thanks for such a quick response.
I have actually tried connecting the Power of Attorney tracker to both to see what might happen. So when it didn't work on Company Bridge, I tried Company information and then I tried Company Bridge.
I think as I fall into the category of "citizen" powerBI user, you're right, there is likely some fudging going on here. I'll do some research into a Star Schema when I have a bit more time as it seems like a key development point in my skill set going forward.
In the meantime, do you think there is anything I could do here, and if not, would you be able to give me a quick understanding of what, in this example, would go into the dimension table?
Thanks again!!!
