Forum Discussion

jrscaletta's avatar
jrscaletta
Icon for Helper II rankHelper II
7 years ago
Solved

Relationship problem working with data from Dynamics365

Hi all.    I'm unable to recover the data that I need from Dynamics365 PSA. I want to show in one matrix the data from 2 database tables. This is my structure:     f   Then, I use PR...
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    Ahh, that makes much more sense.

     

    I added the following measure to your file, and added that as a value to the matrix.  The numbers look right to me, but I don't know your dataset:

     

    msdyn_quantity_measure = CALCULATE(SUM([msdyn_quantity]), USERELATIONSHIP(msdyn_journallines[_msdyn_task_value], msdyn_projecttasks[msdyn_projecttaskid]))

    This measure is forcing the sum to use the relationship described instead of whatever the default active one is. 

Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

What data/fields are you trying to show in the matrix?  I opened your file and was able to get various fields from all tables to show up in the matrix as I would expect them.  If you could show some sample expected output (mocking it up in excel or something) that would be very helpful.

  • jrscaletta's avatar
    jrscaletta
    Icon for Helper II rankHelper II
    7 years ago

    Hi Cmcmahan, 

     

    Thanks for your quickly response!

    I reviewed the PBIX and it's wrong. You're ok.

     

    I need to obtain "msdyn_journallines[msdyn_quantity]" by "msdyn_projecttasks[msdyn_subject]", filtered by project.

    The relationships need to be "msdyn_projectapprovals[_msdyn_referencejournalline_value]" to "msdyn_journallines[msdyn_journallineid]".

     

    Then, I can relation "msdyn_projectapprovals[_msdyn_projecttask_value] to "msdyn_projecttasks[msdyn_projecttaskid]".

     

    Then, my table object:

    Columns: 

    - msdyn_projecttasks[msdyn_subject]

    - msdyn_journallines[msdyn_quantity] .. now appears total on every line. I need by "msdyn_projecttasks[msdyn_subject]"

     

    I attach the new PBIX file: PBIX FILE2

     

    Thanks!

    • Cmcmahan's avatar
      Cmcmahan
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      Ahh, that makes much more sense.

       

      I added the following measure to your file, and added that as a value to the matrix.  The numbers look right to me, but I don't know your dataset:

       

      msdyn_quantity_measure = CALCULATE(SUM([msdyn_quantity]), USERELATIONSHIP(msdyn_journallines[_msdyn_task_value], msdyn_projecttasks[msdyn_projecttaskid]))

      This measure is forcing the sum to use the relationship described instead of whatever the default active one is.