Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eburke
Helper II
Helper II

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

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
Ashish_Mathur
Super User
Super User

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/

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

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/

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

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.

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.