Forum Discussion

Titus_D's avatar
Titus_D
Helper I
4 years ago

Interconnected tables D365

Hi Experts,

I am trying to connect D365 sales with PowerBI, its all connected and seems like we have to maually setup relationships between each table and i have done that as well. While visualising i am falling into a problem and i desperately need help, below is the simplified version of my problem.

 

I have connected System user to Accounts, System user to opportunity and Account to opportunity as well. I am putting a bar graphs on number of accounts by owner and it works well and the data is accurate, i am also trying to put another bar graph on number of Opportunity by Owner and this is where is data is inaccurate, Power BI is not giving me results based on System user table and Opportunity rather its going through Accounts. I want PowerBI to hit System user directly since it is also connected to Opporunity because an opportunity can be owned by a person who is not neceassirily the account owner as well. Basically anyone can own an opportunity even if the account its under is not theirs. 

 

I am not sure how to model this, kindly help. Thanks in advance for your response.

Need Help

3 Replies

  • Titus_D's avatar
    Titus_D
    Helper I
    4 years ago

    Adding more info.

    This is how the model looks

    This is how the data looks

     

    The account data is accurate but the Opportunity data is not, it should be 85,77, 95,81 for each month respectively. 

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      Hi Titus_D ,

       

      Please try one of the following two ways.
      1. Change the relationship between the "accounts" table and the "opportunities" table to inactive, and then change the relationship between the "systemusers" table and the "opportunities" table to active.
      2. Use the following measure to calculate the opportunity value.

      Measure =
CALCULATE (
    SUM ( 'opportunities'[opportunityid] ),
    USERELATIONSHIP ( 'systemusers'[userid], 'opportunities'[userid] ),
    ALL ( 'accounts' )
)

       

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Titus_D's avatar
    Titus_D
    Helper I
    4 years ago

    Hi Team,

     

    Thank you very much for your response, i used Count instead of Sum and i got the desired results. I have two questions.

    1. Can we build everything with inactive relationship and use the "USERELATIONSHIP" and add measures accordingly, will it do anything good or bad

    2. What does "ALL('accounts')" in the measure does.