Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I have two tables with a one to many relationship. The first table is a list of transactions where the order ID is unique.
Table 1
Order ID | customer ID | order date |
123 | ABC | 2022-12-14 |
456 | DEF | 2022-11-11 |
789 | GHI | 2022-10-31 |
The second table uses the order id in the first table to link product holdings. The order ID is not unique in this table
Table 2
Order ID | order date | Customer ID | loan name | loan end date | product name |
123 | 2022-12-14 | ABC | Gibson SG | ||
123 | 2022-12-14 | ABC | 24 month repayment | 2024-12-14 | |
123 | 2022-12-14 | ABC | Fender Telecaster | ||
456 | 2022-11-11 | DEF | 12 month repayment | 2023-11-11 | |
789 | 2022-10-31 | GHI | Fender Jazzmaster |
I would like to create two new columns in the first table. One which has a flag of whether the customer has an active loan or not, and one which lists all of the product holdings like so:
Order ID | customer ID | order date | has active loan | products |
123 | ABC | 2022-12-14 | True | Gibson SG, Fender Telecaster |
456 | DEF | 2022-11-11 | True | |
789 | GHI | 2022-10-31 | False | Fender Jazzmaster |
Usually for a one to one relationship I would do something like:
has_active_loan =
IF(RELATED(TABLE 2[loan name]) <> "" &&
RELATED(TABLE 2[loan end date ]) > RELATED(TABLE 2[order date], True, False)
But I am having some trouble with figuring out the DAX for handeling the one to many relationship.
Any help would be appreciated.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
One of ways to solve this is to use RELATEDTABLE DAX function.
Has active loan CC =
COUNTROWS (
FILTER ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[loan name] <> BLANK () )
) > 0
Products CC =
CONCATENATEX ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[product name], ", " )
Hi,
Please check the below picture and the attached pbix file.
One of ways to solve this is to use RELATEDTABLE DAX function.
Has active loan CC =
COUNTROWS (
FILTER ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[loan name] <> BLANK () )
) > 0
Products CC =
CONCATENATEX ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[product name], ", " )
Fantastic, thank you for your help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
10 | |
10 | |
9 | |
8 |
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
8 |