Relationship VS lookup - Multiple tables

[Anonymous]

I have this issue and a solution, but I want to know if anyone here has a better suggestion while at the same time share my solution.

I have data from Microsoft CRM, in Dataverse (former CDS). I'm using these tables:

• Systemuser
• Account
• Opportunities

To simplify, let's say I have only these columns in my tables.

Only 1 user can be the owner of an entire account.

A user can be the owner of an opportunity regardless of the account it belongs to.

An opportunity is always connected to an account (never multiple accounts though).

An account can have several opportunities.

This creates a circular issue when trying to relate the tables, as demonstrated below.

The main goal is to create a visual table for the user where a sales representative drill down in their own accounts as well as their own opportunities.

So solve it right now, I disregard the relationship and instead I've created a lookup column in my Opportunites table that simply lists the name of the owning user and then I use that column in the reports visual tables.

The column DAX forumula looks like this (allthough modified here to fit in my example above):

Owner = LOOKUPVALUE(Systemusers[Name],Systemusers[UserID],Opportunity[OwnerID])

Is there a better way to solve this issue?

[parry2k]

@Anonymous so this is the result you are looking for:

if yes, change the relationship like this

if this is not what you want, please explain further what I'm doing wrong here.

[Anonymous]

It doesn't quite fix my issue. With this relationship, I can no longer determine what opportunity belongs to which customer (Account).

I need to follow these rules.

1. Only 1 user can be the owner of an entire account.
2. A user can be the owner of an opportunity regardless of the account it belongs to.
3. An opportunity is always connected to an account (never multiple accounts though).
4. An account can have several opportunities.

[parry2k]

@Anonymous why not go ahead and put a sample pbix file with expected output and will get back to you with the solution. 

[Anonymous]

You're right. I should lead with that next time. I also see now that my "table" refered to above is actually the Matrix.. I mixed them up. My bad.

Here is a link to download a sample file!

Here's a picture of my relationships. I can't have one of them active due to the other.

This is what I want to show! The sales rep as a row header and the name of the opportunities beneath.

As you can see, we own 2 opportunities each.

If I now visualize who owns each account, I will instead see the owner of opportunities under each account.

If I change the relationship so that I have a connection between user and account, every opportunity will belong to the user who owns the account regardless of who actually is the owner of that specific opportunity.

This is where I instead make a lookup in the opportunity table, keeping the relationship for accounts.

The result is what I want, but I have to remember to use the Owner-column from the Opportunity table when displaying the name of a user for that purpose but now I don't have a dynamic relationship between the user owning an opportunity and other tables that could be related to the user table.

Let's for (a weird) example say that I have yet another table holding the favorite colors of each users and the users ID. By relating this table to my Systemuser-table, I could now find out which accounts are owned by people whose favorite color is red. I would however not be able to find the opportunities where the owner of that opportunity favors the color red.

[parry2k]

@Anonymous may be create a measure and use userrelationship  DAX function in it. So create relationship between user and account and oppy table, set relationship between account and oppy table which will be inactive relationship and then add a measure

Count Oppy = CALCULATE ( COUNTROWS( Oppy ), USERELATIONSHIP ( Account[Account], Oppy[AccountId] ) ) 

you can use this measure to have a visual level filter or put this measure in the visual to get the count.

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.⚡

[Anonymous]

Sorry I was perhaps not clear on what I wanted to acheive with this. I'm not looking for amounts or summaries but rather the actual name of the opportunity as well as the name of the account listed under each sales rep in a litteral table (visualised in the report as litteraly a table).

Level 1 might be the sales representatives full name and level 2 might be the name of the opportunity (Rows).

Values will then be the estimated value for earch opportunity in this case.

On a similar table (again, the visualisation called table) I want the same setup but this time we switch out opportunity for account name and the value will be the summarized value of all the opportunities connected to that account, which in term is owned by a sales representative.