Forum Discussion

jrscaletta's avatar
jrscaletta
Helper II
7 years ago
Solved

Relationship problem

Hi all!

 

I've a problem with relationship direction:

 

 

Table1: Projects (Filter)

Table 2: ProjectTasks (Primarykey)

Table 3: Actuals - time dedicated by task (Foreign key)

 

The problem is that are some lines on Actuals that doesn't have ProjectTask associate, but yes Project. Then, when I show "msdyn_quantity" from data, it shows the total time, not by task. That's is OK.

 

Then, I created a new Measure (himputadas), but it doesn't show the lines from actuals that not have ProjectTask associate. The time difference is my problem.

 

My relations:

 

My data:

196,82 is correct, but himputaciones need to show 196,82 too, with a line with msdyn_subject in blank for the difference on actuals that don't have projecttask associate.

 

 

My measure: 

hImputadas = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);USERELATIONSHIP(msdyn_actuals[_msdyn_task_value];msdyn_projecttasks[msdyn_projecttaskid]))

I tried too with CROSSFILTER and changing relations direction.

I attach PBIX file: PBIX FILE

 

Thanks in advance!

 

 

 

 

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    jrscaletta update relationship and measure as below

     

    hImputadas = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]),
USERELATIONSHIP(msdyn_actuals[_msdyn_project_value], msdyn_projects[msdyn_projectid]),
CROSSFILTER(msdyn_actuals[_msdyn_project_value], msdyn_projects[msdyn_projectid], Both ))

     

     

     

    here is the output

     

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    jrscaletta update relationship and measure as below

     

    hImputadas = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]),
USERELATIONSHIP(msdyn_actuals[_msdyn_project_value], msdyn_projects[msdyn_projectid]),
CROSSFILTER(msdyn_actuals[_msdyn_project_value], msdyn_projects[msdyn_projectid], Both ))

     

     

     

    here is the output

     

    • jrscaletta's avatar
      jrscaletta
      Helper II
      7 years ago

      Hi parry2k, thanks for your help! It works!

      But now, I've seen is not showing any line for the "msdyn_subject" that has "hImputadas" in blank. I need to show them too.

       

      It works if I change the direction of the relationship (msdyn_projecttask - msdyn_actuals) from BOTH to ONE WAY, but it brokes other information. I tried to create a new measure like:

       

       
      hImputadasDetallTarea = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);
CROSSFILTER(msdyn_projecttasks[msdyn_projecttaskid];msdyn_actuals[_msdyn_task_value];OneWay))
       

       

      But it doesn't work equals that when I changed the relationship direction on the model.

       

      Do you know how can I do that?

       

      Thanks in advance!

      • jrscaletta's avatar
        jrscaletta
        Helper II
        7 years ago

        I tried too using this measure:

         

         

        hImputadasDetallTarea = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);
CROSSFILTER(msdyn_actuals[_msdyn_task_value];msdyn_projecttasks[msdyn_projecttaskid];Both);
CROSSFILTER(msdyn_projecttasks[_msdyn_project_value];msdyn_projects[msdyn_projectid];Both))

         

         

        But I think it's necessary use "USERELATIONSHIP" too.

        With this measure not work:

        - Not appear msdyn_projecttasks info that no has msdyn_actuals[msdyn_quantity] data.

        - Not appear msdyn_actuals info that no has msdyn_projecttasks data.

         

        My schema:

         

         

        With this measure:

         

         

         

        hImputadasDetallTarea = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);
CROSSFILTER(msdyn_actuals[_msdyn_task_value];msdyn_projecttasks[msdyn_projecttaskid];Both);
CROSSFILTER(msdyn_projecttasks[_msdyn_project_value];msdyn_projects[msdyn_projectid];Both))

         

        With this measure not work:

        - Not appear msdyn_projecttasks info that no has msdyn_actuals[msdyn_quantity] data.

        - Not appear himputadasDetallTarea info that no has msdyn_projecttasks data.

         

        Photo:

         

        If I use this measure:

        hImputadas = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);
USERELATIONSHIP(msdyn_actuals[_msdyn_project_value]; msdyn_projects[msdyn_projectid]);
CROSSFILTER(msdyn_actuals[_msdyn_project_value]; msdyn_projects[msdyn_projectid]; Both ))

        It shows:

        - msdyn_actuals[quantity] that no have msdyn_projecttasks data: CORRECT.

         

        But no shows:
        - Not appear msdyn_projecttasks info that no has msdyn_actuals[msdyn_quantity] data.

         

        How can I do?

         

        Thanks in advance.

  • jrscaletta's avatar
    jrscaletta
    Helper II
    7 years ago

    Hi All!!

     

    Table1: Projects (Filter)

    Table 2: ProjectTasks (Primarykey)

    Table 3: Actuals - time dedicated by task (Foreign key)

     

    The problem is that are some lines on Actuals that doesn't have ProjectTask associate, but yes Project. Then, when I show "msdyn_quantity" from data, it shows the total time, not by task. That's is OK.

     

    Then, I created a new Measure (himputadas), but it doesn't show the lines from actuals that not have ProjectTask associate. The time difference is my problem.

     

    My relations:

     

    My data:

    196,82 is correct, but himputaciones need to show 196,82 too, with a line with msdyn_subject in blank.

     

     

    My measure: 

    hImputadas = CALCULATE(SUM(msdyn_actuals[msdyn_quantity]);USERELATIONSHIP(msdyn_actuals[_msdyn_task_value];msdyn_projecttasks[msdyn_projecttaskid]))

    I tried too with CROSSFILTER and changing relations direction.

    I attach PBIX file: PBIX FILE

     

    Thanks in advance!