Forum Discussion

srennie's avatar
srennie
Regular Visitor
3 years ago
Solved

USERELATIONSHIP Function to Align Dates

I have two datasets that I want to be able to filter with the same date filter - there is no clear link between the two datasets so after some research I discovered the USERELATIONSHIP Function might be the answer but I don't think I'm using it correctly.

 

I have the below measure but when I filted with the 'Calendar'[Date] I get an incorrect value but when I filter by date in the original dataset Defect_Data[Creation Date] I get the correct value.

 

Can anyone point me in the correct direction?

 

Escaped Defects =

CALCULATE(DISTINCTCOUNT(Defect_Data[Defect #]), USERELATIONSHIP('Calendar'[Date], Defect_Data[Creation Date]), FILTER(Defect_Data, Defect_Data[Supplier Defect] = "N"))

  • srennie , Try like

     

    CALCULATE(CALCULATE(DISTINCTCOUNT(Defect_Data[Defect #]), FILTER(Defect_Data, Defect_Data[Supplier Defect] = "N")), USERELATIONSHIP('Calendar'[Date], Defect_Data[Creation Date]))

2 Replies

  • srennie , Try like

     

    CALCULATE(CALCULATE(DISTINCTCOUNT(Defect_Data[Defect #]), FILTER(Defect_Data, Defect_Data[Supplier Defect] = "N")), USERELATIONSHIP('Calendar'[Date], Defect_Data[Creation Date]))

    • srennie's avatar
      srennie
      Regular Visitor

      amitchandak That seems to have worked - thank you! Can you explain why you need to use the function this way?