Forum Discussion

eburke's avatar
eburke
Helper II
8 years ago
Solved

Userelationship and Measures

Hi all,   Hoping someone can help, I've just worked out how to use the Userelationship function to allow me to link two tables multiple times.  I have a data table that has several date columns = E...
Need Help
  • eburke's avatar
    eburke
    8 years ago

    Ok I've had some success, looked at it from a different angle and did it in two steps.  Started with the measure that links the relationship so it will appear correctly in the month table:

     

    Enquiry Date = CALCULATE(COUNTA(Leads[EnquiryDate]),USERELATIONSHIP(Leads[EnquiryDate],'Work Calendar'[Date]))

     

    Then used this measure in the calculate to only look at calls without an approval date, rather than using the measure All Calls.

     

    Enquiries = CALCULATE([Enquiry Date], Leads[DateActiveReferralCodeReceivedByOfficer] = BLANK())

     

    This way I get the benefit of both calculations in the final measure.  Maybe not what is considered elegant programing but it works which is the most important thing for me at the moment.

     

    Thanks everyone for your input.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi eburke,

 

Try this

 

=CALCULATE([All Calls],FILTER(Leads,ISBLANK(Leads[DateActiveReferralCodeReceivedByOfficer])),USERELATIONSHIP(Leads[EnquiryDate],'Work Calendar'[Date]))

 

Hope this helps.

eburke's avatar
eburke
Helper II
8 years ago

Thanks for your reply Ashish, unfortunately that gets the error message "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2."

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi eburke,

     

    I just edited my formula in the previous reply.  Copy the revised formula.

    • eburke's avatar
      eburke
      Helper II
      8 years ago

      Ok I've had some success, looked at it from a different angle and did it in two steps.  Started with the measure that links the relationship so it will appear correctly in the month table:

       

      Enquiry Date = CALCULATE(COUNTA(Leads[EnquiryDate]),USERELATIONSHIP(Leads[EnquiryDate],'Work Calendar'[Date]))

       

      Then used this measure in the calculate to only look at calls without an approval date, rather than using the measure All Calls.

       

      Enquiries = CALCULATE([Enquiry Date], Leads[DateActiveReferralCodeReceivedByOfficer] = BLANK())

       

      This way I get the benefit of both calculations in the final measure.  Maybe not what is considered elegant programing but it works which is the most important thing for me at the moment.

       

      Thanks everyone for your input.

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Community Support
        8 years ago

        eburke,

         

        Since your problem has been resolved, please help accept solution. Your contribution is highly appreciated.

    • eburke's avatar
      eburke
      Helper II
      8 years ago

      his came up with a blank result.  That's ok, I'll go with my two step approach, appreciate the help.