Need help to create a model/relationship with Account Owner who covers multiple accounts by States

[dstoltz11]

Hello, 

I am having issues creating a model with the correct relationships for Account Owners who cover multiple Accounts, in which some are covered Nationally and the others by specific States. 

Here's an example of a table that I've created but when I use it in Power BI, it show's the Accounts 'Sales Revenue' Nationally not by the indicated States. 

This is my first Power BI report, my first post, my first data model, so consider me an amateur. 🙂 

What would help me solve my problem here? Do I need to use DAX or is it a simpler answer to my issue?

The relationships are tied to the PPID or "Account ID". But if an Account Owner only covers that Account through assigned States, how do I indicate that within the model/relationships? 

Also, I had to create an Alternative Table to join Partner Assignments to SalesData because some Account Owners cover the same Account in different states. 

Thanks! 
DS

[dax]

Hi dstoltz11, 

I am not clear  about your detailed requirement, so if possible, could you plesae inform me more detailed infromation(your sample data and your expected output)? If you want to create relationship between two tables, I think you need to make sure you have complete mapping table, which will help you create relationship.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentyler]

Here's where moving your data model from the way a human being would think about things to the way a computer thinks about things would be helpful.

Consider this example

You create one record for each account/owner/state combination.
The easiest way to do this is in the data source (excel or sql) before loading the data. If you cannot control the data source you could create a fairly complicated query in M to get the same result. If that's the situation you're in let us know and we can provide further help.
Making this change in the data model will probably eliminate almost all the need to write any dax to generate your reports.
If you'd like to know more about how to use this kind of table in your reports... or how to generate it I'd be glad to do a screen share. Just send me your email address and a good day and time (I'm ken@8thfold.com)