Related table

[Smile23]

Hello i have a Problem with related Tables

I have a table a with

Product id

Manager id

Costs

And a table b with

Product id

Manager id

And a percentage split.

In other words, if there is a value in table b, the costs (table a) should be divided by the percentage key (table b) - which also changes the manager of table a to that of table b (One manager ID then becomes two). If there is no entry for a project, the costs and the manager ID from table A remain

Do you please give me an idea of ​​what a solution could look like? Thanks

[Anonymous · Accepted solution]

Hi  @Smile23 ,

I created some data:

TableA:

TableB:

Here are the steps you can follow：

1. Create calculated column.

Step1 =
var _TableBProduct=
SELECTCOLUMNS(
    'TableB',"Product",'TableB'[Poduct id])
return
IF(
    'TableA'[Poduct id] in _TableBProduct,
    'TableA'[Costs] *
    SUMX(
        FILTER(ALL('TableB'),
        'TableB'[Poduct id]=EARLIER('TableA'[Poduct id])),[Percentage split]),
        'TableA'[Costs])

Step2 =
var _TableBProduct=
SELECTCOLUMNS(
    'TableB',"Product",'TableB'[Poduct id])
return
IF(
    'TableA'[Poduct id] in _TableBProduct,
    MAXX(
        FILTER(ALL('TableB'),
        'TableB'[Poduct id]=EARLIER('TableA'[Poduct id])),'TableB'[Manage id]),'TableA'[Manage id])

 2. Result:

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly