cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliceW
Post Prodigy
Post Prodigy

New column in a table based on another table in a one-to-many relationship

Hi everyone,

I can't figure this out, and so your help would be very much appreciated.

What I have:

I have one table, 'Account', in a one-to-many relationship with another table, 'Opportunities'.

There are two relationships, both one-to-many:

- Account ID to Billing Account ID (active relationship);

- Account ID to Supported Account ID;

An Account ID in the 'Account' table can have zero, one, ore more Opportunities.

 

What I need:

Another column in the 'Account' table which gives a 'Yes' when the Account ID is linked to at least one Supported Account ID and 'No' when it does not.

 

What I've tried:

RELATED and USERELATIONSHIP. The latter because I need to use the non-active relationship.

 

Could a table function work? If the Supported Account ID count is >0 or not blank, then 'Yes'? They are my weak point.

 

Big thanks in advance,

Alice

1 ACCEPTED SOLUTION

Certainly...

IF(
	CALCULATE(
		COUNTROWS(RELATEDTABLE(Opportunities)),
		USERELATIONSHIP(I don't know the keys so insert here)
	) > 0,
	TRUE(),
	FALSE()
)

View solution in original post

6 REPLIES 6
AliceW
Post Prodigy
Post Prodigy

It was such a big blocker for me - I really appreciate you taking the time to help me out.

littlemojopuppy
Super User
Super User

De nada...glad I could help!

littlemojopuppy
Super User
Super User

Try this...

IF(
	COUNTROWS(RELATEDTABLE(Opportunities)) > 0,
	TRUE(),
	FALSE()
)

Thank you! Any thoughts on how to implement USERELATIONSHIP? I need to use the non-active relationship between the tables...

Certainly...

IF(
	CALCULATE(
		COUNTROWS(RELATEDTABLE(Opportunities)),
		USERELATIONSHIP(I don't know the keys so insert here)
	) > 0,
	TRUE(),
	FALSE()
)

View solution in original post

THANK YOU, Mojo!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.