New Columns through m to n relation

[Anonymous]

Hi,
I am new to the forum and have the following problem here. I have a m to n relationship between two tables. Now I want to check some conditions before writing anything from table A to table B. If you can see below, there is no unique key that it could be a many to many relation here. 

In the first step, I duplicated the "Value" column in Table A and added "Fee" and "Add. Information" columns. I do not know if this was a mistake. The goal here is from table A to write the information "Fee & Add. Information" to table B. I have already tried this with a lookupvalue or Calculate, but I am still too far from the solution for me to say anything is working. I'm also not sure if I'd rather build another table or something else. Please help.

Table A - Concession Table:

Door Nr.	Brand Nr.	Supplier Nr.	Sortiment Nr.	Field description	Value	Fee	Add. Information
232	12345	7584	20	Fee	12	12	Null
123	12345	7584	20	Add. Information	Infos	Null	Infos
232	1234	3456	10	Fee	32	32	Null
123	1234	3456	10	Fee	43	43	Null

Table B - Sales Table:

Door Nr.	Brand Nr.	Supplier Nr.	Sortiment Nr.	Kind of purchase	Net Sales	Fee	Additional Information
232	12345	7584	20	Concession	100	?	?
232	12345	7584	10	Concession	200	?	?
232	12345	6859	20	Concession	30	?	?
123	1234	3456	10	Concession	40	?	?

Thanks in advanced,

Sebastian

[Anonymous]

HI @Anonymous,

Perhaps you can take a look at the following blog to create a bridge table for many to many relationship table records mappings:

How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

Regards,

Xiaoxin Sheng

[Anonymous]

Sorry, but this is a total another problem. And the solution is from 2019 where no many to many relationship in Power BI exist.

[Anonymous · Accepted solution]

HI @Anonymous,

Do you mean this requirement is more related to lookup values from another table based on current table field values? If that is the case, you can try to use the following formals to get value from other tables:

Fee =
CALCULATE (
    SUM ( Concession[Fee] ),
    FILTER (
        Concession,
        [Door Nr.] = EARLIER ( Sales[Door Nr.] )
            && [Brand Nr.] = EARLIER ( Sales[Brand Nr.] )
            && [Supplier Nr.] = EARLIER ( Sales[Supplier Nr.] )
            && [Sortiment] = EARLIER ( Sales[Sortiment] )
    )
)

Additional Information =
CALCULATE (
    CONCATENATEX (
        VALUES ( Concession[Add. Information] ),
        [Add. Information],
        ","
    ),
    FILTER (
        Concession,
        [Door Nr.] = EARLIER ( Sales[Door Nr.] )
            && [Brand Nr.] = EARLIER ( Sales[Brand Nr.] )
            && [Supplier Nr.] = EARLIER ( Sales[Supplier Nr.] )
            && [Sortiment] = EARLIER ( Sales[Sortiment] )
    )
)

Regards,

Xiaoxin Sheng