Forum Discussion

juliamacg_'s avatar
juliamacg_
Frequent Visitor
2 years ago
Solved

Help needed with AVERAGEX and USERELATIONSHIP - date slicer not working

Hello. I have a table called FAT_REGISTRY on PBI with many rows regarding my company's contracts. It has many duplicates for CONTRACT_ID and other fields due to columns like UPDATE_DATE and others. ...
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    juliamacg_ 
    I woudl suggest you to try this pattern:

    Completed Tasks Average Duration = 

CALCULATE(

    AVERAGEX(

       VALUES(FAT_REGISTRY[TASK_ID]),

        calculate(Sum(FAT_REGISTRY[TASK_DURATION]))

    ),

    FAT_REGISTRY[TASK_COMPLETION_DATE] <> BLANK(),

   USERELATIONSHIP(DIM_CALENDARIO[Date],FAT_REGISTRY[TASK_COMPLETION_DATE])

)

     

    And regarding the issue with USERELATIONSHIP function, would it be possible for you share the sample file?

     

tharunkumarRTK's avatar
tharunkumarRTK
Icon for Super User rankSuper User
2 years ago

juliamacg_ 
I woudl suggest you to try this pattern:

Completed Tasks Average Duration = 

CALCULATE(

    AVERAGEX(

       VALUES(FAT_REGISTRY[TASK_ID]),

        calculate(Sum(FAT_REGISTRY[TASK_DURATION]))

    ),

    FAT_REGISTRY[TASK_COMPLETION_DATE] <> BLANK(),

   USERELATIONSHIP(DIM_CALENDARIO[Date],FAT_REGISTRY[TASK_COMPLETION_DATE])

)

 

And regarding the issue with USERELATIONSHIP function, would it be possible for you share the sample file?

 

juliamacg_'s avatar
juliamacg_
Frequent Visitor
2 years ago

tharunkumarRTK I tried your solution but the numbers turned out huge, so I changed the SUM part into AVERAGE and it worked!

 

Completed Tasks Average Duration = 

CALCULATE(

    AVERAGEX(

       VALUES(FAT_REGISTRY[TASK_ID]),

        CALCULATE(AVERAGE(FAT_REGISTRY[TASK_DURATION]))

    ),

    FAT_REGISTRY[TASK_COMPLETION_DATE] <> BLANK(),

   USERELATIONSHIP(DIM_CALENDARIO[Date],FAT_REGISTRY[TASK_COMPLETION_DATE])

)

 

Thanks so much!