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
Nosh10
Helper II
Helper II

Conditional Formatting by Unrelated Tables

Hello!

 

I need a push solving a problem. 

I have two tables; Table 1: customer ID and email address; Table 2: supplier ID and email address. I would like to show: IF the same email address is in table 1 and table 2, AND have a customer ID and service ID, show email addresses 

 

Table 1
Customer     ID Email Address
334               aa@gmail.com
                     bb@gmail.com
454               cc@gmail.com
122              dd@gmail.com
                    ee@gmail.com
678              ff@gmail.com
                    gg@gmail.com
                    hh@gmail.com
444              ii@gmail.com
876              jj@gmail.com
444             kkgmail.com

Table 2
Supplier      ID Email Address
1113              aa@gmail.com
1112              bb@gmail.com
                      cc@gmail.com
2345              dd@gmail.com
1111              ee@gmail.com
1000              ff@gmail.com
1100             gg@gmail.com
                     hh@gmail.com
                     ii@gmail.com
5678             jj@gmail.com
2000            kkgmail.com

Table 3 - I'm looking for this result (output) Table 3
aa@gmail.com
dd@gmail.com
ff@gmail.com
jj@gmail.com
kkgmail.com

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Nosh10 ,

 

Try this:

Table =
VAR t =
    FILTER (
        CROSSJOIN ( 'Table 1', 'Table 2' ),
        'Table 1'[ID Email Address] = 'Table 2'[ID Email Address]
            && 'Table 1'[Customer] <> BLANK ()
            && 'Table 2'[Supplier] <> BLANK ()
    )
RETURN
    SELECTCOLUMNS ( t, "Email Address", 'Table 1'[ID Email Address] )

email.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

1 REPLY 1
Icey
Community Support
Community Support

Hi @Nosh10 ,

 

Try this:

Table =
VAR t =
    FILTER (
        CROSSJOIN ( 'Table 1', 'Table 2' ),
        'Table 1'[ID Email Address] = 'Table 2'[ID Email Address]
            && 'Table 1'[Customer] <> BLANK ()
            && 'Table 2'[Supplier] <> BLANK ()
    )
RETURN
    SELECTCOLUMNS ( t, "Email Address", 'Table 1'[ID Email Address] )

email.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

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.