Forum Discussion

Smile23's avatar
Smile23
New Member
2 years ago
Solved

Related table

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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