Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
purple_SP
Helper I
Helper I

Returning a single value from a one-to-many relationship

Hello I have two tables. The first is a list of transactions which represent customers changing their product service from one thing to another. The order ID is unique in this table.

 

Order ID original product id new product id change date 
123 875 02/11/2022 
561 123 04/09/2022 
454 561 10/10/2022 

 

The second table is a list of all products that the customer has and the date that each product service started. In this table the order id is not unique.

 

 

Order ID customer id product id start date
123 02/09/2021
699 06/06/2021
747 10/04/2021
561 01/01/2022
123 06/07/2020
454 05/08/2021

 

I would like to create a new column in the first table which has the date that the original plan was created like so:

 

Order ID original product id new product id change date original product start date
1  123  875 02/11/2022 02/09/2021
561 123 04/09/2022 01/01/2022
454 561 10/10/2022 05/08/2021

 

I have tried using CALCULATE with RELATEDTABLE and FILTER, kind of like this:

CALCULATE (
    FIRSTDATE( table2[start date]),
    FILTER ( RELATEDTABLE( table2), table2[product id] = table1[original product id] && table2[order id] = table1[order id])
)

But this results in a 'Circular dependency detection error'.

I have also tried the LOOKUPVALUE function like so:

 
LOOKUPVALUE(
    table2[start date],
    table2[order id],table1[order id],
    table2[product id],table1[original product id]
)

But this results in the multiple values being returned : "A table of multiple values was supplied where a single value was expected."

 

Any help would be much appreciated.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1671524823174.png

 

 

Jihwan_Kim_0-1671524802627.png

 

 

Original product start date CC =
MAXX (
    FILTER (
        RELATEDTABLE ( Table2 ),
        Table2[product id] = Table1[original product id]
    ),
    Table2[start date]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
purple_SP
Helper I
Helper I

This did the trick. Thanks for your help!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1671524823174.png

 

 

Jihwan_Kim_0-1671524802627.png

 

 

Original product start date CC =
MAXX (
    FILTER (
        RELATEDTABLE ( Table2 ),
        Table2[product id] = Table1[original product id]
    ),
    Table2[start date]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors