Forum Discussion

jrscaletta's avatar
jrscaletta
Helper 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 PROJECT as a Filter.

When I try to show in a same matrix object data from "msdyn_projecttasks" and "msdyn_journallines", matrix become blank.

 

How can I resolve that? I attach de PBIX file: PBIX FILE

 

Thanks in advance.

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. 

7 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident 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
      Helper 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
        Resident 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. 

  • jrscaletta's avatar
    jrscaletta
    Helper II
    7 years ago

    Anyone knows how to work with this type of relation?

     

    Thanks in advance!

    • jrscaletta's avatar
      jrscaletta
      Helper II
      7 years ago

      Sorry to insist, but I'm sure that someone know this easy question.


      Thanks in advance.

    • Cmcmahan's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

      Did my solution not work?  What issue are you still having?