cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
eburke Regular Visitor
Regular Visitor

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 = 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

1 ACCEPTED SOLUTION

Accepted Solutions
eburke Regular Visitor
Regular Visitor

Re: Userelationship and Measures

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.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Userelationship and Measures

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
eburke Regular Visitor
Regular Visitor

Re: Userelationship and Measures

Enquiry.PNG

 

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?

Super User
Super User

Re: Userelationship and Measures

Hi @eburke,

 

Try this

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
eburke Regular Visitor
Regular Visitor

Re: Userelationship and Measures

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."

Super User
Super User

Re: Userelationship and Measures

Hi @eburke,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
eburke Regular Visitor
Regular Visitor

Re: Userelationship and Measures

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.

View solution in original post

eburke Regular Visitor
Regular Visitor

Re: Userelationship and Measures

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

Community Support Team
Community Support Team

Re: Userelationship and Measures

@eburke,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,450)