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.

 

I need to have a measure that calculates the average task duration for each task and on account of the table's structure I used AVERAGEX for that. 

 

Completed Tasks Average Duration = 

CALCULATE(

    AVERAGEX(

        SUMMARIZECOLUMNS(FAT_REGISTRY[TASK_ID], FAT_REGISTRY[TASK_DURATION]),

        FAT_REGISTRY[TASK_DURATION]

    ),

    FAT_REGISTRY[TASK_COMPLETION_DATE] <> BLANK(),

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

)
 
The USERELATIONSHIP part is there because DIM_CALENDARIO[Date] is the field I use in all slicers. I actually know it's set alright because I used that same relationship in many other measures and in them the USERELATIONSHIP function is working fine.

However in the aforementioned measure it is not. When I plot it in a chart it shows me the result for the whole table instead of applying the date filter I select in the slicer. It is not affected by it at all.

 

I'm lost here. What should I change in the measure for it to work?

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?

     

2 Replies

  • 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!