Скачать или смотреть How to Join 2 Tables in Power BI Using a 3rd Table Condition

Learn how to effectively join multiple tables in Power BI using DAX with a focus on incorporating conditions from a third table. Perfect for beginners!
---
This video is based on the question https://stackoverflow.com/q/64886432/ asked by the user 'Nadhirah21' ( https://stackoverflow.com/u/14658910/ ) and on the answer https://stackoverflow.com/a/64887219/ provided by the user 'Joao Leal' ( https://stackoverflow.com/u/1309027/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to join 2 tables in Power BI with condition involving a 3rd table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Join 2 Tables in Power BI Using a 3rd Table Condition

Joining tables is one of the foundational skills you need while working with data in Power BI. However, if you're new to DAX (Data Analysis Expressions) and Power BI, figuring out how to make complex relationships work can be daunting. In this post, we'll walk through a specific scenario involving three tables: Company, Address, and C_detail, and how to create the necessary relationships and calculations to achieve the desired result.

Understanding the Problem

You have three tables:

Company: Contains information about various companies.

Address: Links companies with their corresponding addresses through a CompanyID.

C_detail: Contains additional details, such as CountryID, which is used to filter the results based on certain criteria.

The Requirement

You want to count how many companies are associated with a specific C_ID from the C_detail table. The SQL equivalent of your query would look something like this:

[[See Video to Reveal this Text or Code Snippet]]

Now, let's find out how to translate this into Power BI using DAX.

Setting Up Relationships

Before we can pull any data or create calculations, we need to establish relationships between the tables in Power BI.

Create Relationships:

Join Company to Address on CompanyID.

Join Address to C_detail on CountryID.

Make sure to set the filter direction to both ways to allow for proper flow of information across your tables.

Creating the Measure

Once your relationships are established, you can create a measure to count the companies based on the specified condition from the C_detail table.

Simple Measure Approach

If you've set up your relationships, you can easily create a measure like this:

[[See Video to Reveal this Text or Code Snippet]]

Then, use a slicer in your Power BI report to filter for the desired C_ID. This method is straightforward and leverages the existing relationships for filtering.

Alternative Measure Without Relationships

If creating relationships is not an option, you can still achieve similar results using another measure. Here’s how to do it:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Alternative Measure

SUMMARIZE: This function generates a summary of the Address table along with a custom "valid" column that counts rows based on the filter.

FILTER: This filters out rows where the "valid" column is greater than zero, ensuring that only relevant company IDs are selected.

TREATAS: This acts like a join, aligning the selected company IDs with the Company table.

Conclusion

By creating the correct relationships in Power BI and employing measures, you can effectively analyze multi-table data even with complex conditions. Whether you opt for a straightforward measure using relationships or go for an alternative approach, Power BI provides the flexibility needed to count companies based on criteria from another table seamlessly.

Feel free to ask questions if you're new to DAX, as diving into data analysis can seem overwhelming at first. Happy reporting!