Userelationship and Measures

[eburke]

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 = Enquiry Date, Lead Date, Conversion Date.  I have linked all of these to my calendar with the Conversion date being the default and the other two being activated by a Userelationship, for example:

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

My issue is that I also need to add a filter to this calculation and don't know how.  For example an enquiry is considered a call without an Active Code, so normally I would have a measure filtering all rows without a code and calling them Enquiries:

Enquiries = CALCULATE([All Calls],Leads[DateActiveReferralCodeReceivedByOfficer] = BLANK())

How do I essentially combine these so I can get a measure that filters the rows by whether or not they have an active code, yet at the same time applies the Userelationship so that the show correctly in a table by months?

Hope that makes sense.  Thanks

[Ashish_Mathur]

Hi @eburke,

Try this

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

Hope this helps.

[eburke]

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]

Hi @eburke,

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

[eburke]

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

[eburke · Accepted solution]

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.

[Anonymous]

What if you passed the Enquiry Date as a variable?

Enquiries = 

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

Return

CALCULATE(_enquiryDate, Leads[DateActiveReferralCodeReceivedByOfficer] = BLANK())

[v-chuncz-msft]

@eburke,

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

[MattAllington]

You can pass as many filters to calculate as you need. Just delete the last bracket in your first formula, then add your leads filter there

[eburke]

Hi Matt, thanks for the answer, unfortunately this gets me a result saying 'False' instead of the number of enquiries per month.  Any idea where I'm going wrong here?