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
jwbtkd3
Frequent Visitor

IF Function - return values from one table if true, values from other table if false

Hello,

 

I have slowly been learning Power BI and this issue I am working on has plagued me for some time now. These are reports that are being pulled a weekly basis for visibility inside my Firm.

 

My data layout is as follows:

 

Table 1 contains information about people, their Employers, and some other dimensional data.

Table 2 contains relational information regarding those people - specifically, who they know in our Firm.

Table 3 contains relational information regarding those Employers - specifically, who has a relationship with the Employers at our Firm.

 

Tables 2 and 3 are exported from their sources in such a way that for each relationship, there is a new row. Examples:

 

Table 2

Full Name   |     Employer           |     Firm Relationshp*

John Smith | Acme Corporation |    Mr. CEO

John Smith | Acme Corporation |    Other Staff Member

 

Table 3

Employer Name     |  Firm Relationship*

Acme Corporation |   Ms. CFO

Acme Corporation |   Ms. CFO's secretary

 

*A relationship can exist between an individual, but not their employer, and vice versa.

 

 

Currently, the data model is setup so there is a 1 to Many relationship between Table 1 and Table 2 via email addresses. Table 1 and Table 3 cannot be setup with a relationship because there is usually a Many to Many relationship (criteria from the data pull for Table 1 can return multiple individuals from the same Employer).

-----------------------------------------------------------------------------------------------------

Example.jpg

GOAL: To have the final column (Connected To) show the relational information from Table 3, if it exists, otherwise Table 2.

 

 If any additional information is needed, please ask. Any and all help is appreciated.

 

Thanks!

 

 

 

Edit for Clarification:

 

Given a [Full Name] from Table 1, if Table 3 [Connection] contains data for [Employer], return [Connection] Values from Table 3 where Table3[Employer Name] = Table1[Employer]. If Table 3 does not contain data for [Employer], return [Connection] Values from Table 2 where Table2[Full Name] = Table1[Full Name].

 

The values will be arranged on a matrix. I have an short sample data set to work with (I apologize it is not longer, I have no experience creating sample data).

 

In my example, the ideal output would be:

 

 

Full Name          | Employer                 | Connection

Adam Fisher      | Salmon Emporium   | Brian Null

                                                             | Jeff Robinson

Amy Smith         | Acme Corp              | William Zheng

Bill Hanson         | Hanson Windows    | John Miller

                                                             | Jody Bank

Bill Weber           | Nancy's Daycare     | Sue Pillow

Branden King     | Hanson Windows    | John Miller

Felicia Day          | Amazon                  | Dan Nuss

George Foreman| We Sell Grills          | Don Ham

                                                            | Howard Donn

Larry Vance        | Salmon Emporium  |Brian Null

                                                            | Jeff Robinson

Nancy Weber     |  Nancy's Daycare    | Sue Pillow

Troy Barnes        | Barnes & Noble      | (blank)

 

 

Link to sample data: https://drive.google.com/open?id=0B59iuV1_JBMUVFI1d3JRYlJRb0k

1 ACCEPTED SOLUTION

Hi @jwbtkd3,

 

I've modified your PBIX file and here is the output.

 

The idea is to Merge the three tables together using the Query Editor.

 

PBIX File

 

The table you are looking for is the one called Final


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

Hi @jwbtkd3,

 

Regarding "To have the final column (Connected To) show the relational information from Table 3, if it exists, otherwise Table 2.", do you mean if related  [Connected To] doesn't exist in Table3, you want to return Table 2, right? Can you elaborate the requirement. It would be better if you can create a .pbix file with sample data for our test.

 

Best Regards,
Qiuyun Yu

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

Hello,

 

I can elaborate on the requirement, yes. Given a [Full Name] from Table 1, if Table 3 [Connection] contains data for [Employer], return [Connection] Values from Table 3 where Table3[Employer Name] = Table1[Employer]. If Table 3 does not contain data for [Employer], return [Connection] Values from Table 2 where Table2[Full Name] = Table1[Full Name].

 

The values will be arranged on a matrix. I have an short sample data set to work with (I apologize it is not longer, I have no experience creating sample data).

 

In my example, the ideal output would be:

 

 

Full Name          | Employer                 | Connection

Adam Fisher      | Salmon Emporium   | Brian Null

                                                             | Jeff Robinson

Amy Smith         | Acme Corp              | William Zheng

Bill Hanson         | Hanson Windows    | John Miller

                                                             | Jody Bank

Bill Weber           | Nancy's Daycare     | Sue Pillow

Branden King     | Hanson Windows    | John Miller

Felicia Day          | Amazon                  | Dan Nuss

George Foreman| We Sell Grills          | Don Ham

                                                            | Howard Donn

Larry Vance        | Salmon Emporium  |Brian Null

                                                            | Jeff Robinson

Nancy Weber     |  Nancy's Daycare    | Sue Pillow

Troy Barnes        | Barnes & Noble      | (blank)

 

 

Link to sample data: https://drive.google.com/open?id=0B59iuV1_JBMUVFI1d3JRYlJRb0k

 

Original Post updated with this data.

Hi @jwbtkd3,

 

I've modified your PBIX file and here is the output.

 

The idea is to Merge the three tables together using the Query Editor.

 

PBIX File

 

The table you are looking for is the one called Final


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

This works like a charm. Awareness of conditional columns would have helped me greatly, but organizing the data was key, too. Thank you for your help 🙂

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.