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. 

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.