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
Velocity
Helper III
Helper III

Unable to create a column based on relationship with another table

Hi,

 

I have a table (Table_A) with multiple values of a field (Field_A). There is anothe table (Distinct_Table_B) with only one field (Field_B). There is a one to many relation built from Field_B to Field_A.

 

I want to create a calculated column in Table_A which says 'True' if it finds matching Field_B for Field_A. 

 

Help requested.

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Velocity 

New column in Table_A

New Column = if(isblank(countx(filter(Distinct_Table_B,Distinct_Table_B[Field_B]= Table_A[Field_A] ),Distinct_Table_B[Field_B])),"No","Yes")

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

In Table_A, create this calculated column formula

=IF(ISBLANK(RELATED(Distinct_Table_B[Field_B])),FALSE(),TRUE())

Hope this helps.


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

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

In Table_A, create this calculated column formula

=IF(ISBLANK(RELATED(Distinct_Table_B[Field_B])),FALSE(),TRUE())

Hope this helps.


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

Thanks @Ashish_Mathur 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@Velocity 

New column in Table_A

New Column = if(isblank(countx(filter(Distinct_Table_B,Distinct_Table_B[Field_B]= Table_A[Field_A] ),Distinct_Table_B[Field_B])),"No","Yes")

Thanks @amitchandak. However, i have not understood the role of countx in this expression. Can you please explain?

I am creating a calculating column to get data from unrelated tables using the choice of filters. So I just counted if it present in another table. It will return blank for no match. We can also use minx and maxx.

 

** unrelated  - the way we are using it as of now.

@amitchandak 

...but since the tables are related (There is a one to many relation built from Field_B to Field_A), is there a better way of handling it? When there are millions of records in Table_A, it will be very time consuming.

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.