Relationship between 2 tables and more than 2 columns

[Anonymous]

Hello everybody,

Please support me with as per below:

I have two main tables ,

I need to put in relationship these two tables through the columns "Code" in Table1 and "Code Start" and "Code End" in Table2.

Purpose is to final summarize the number of "candies", taking in consideration these two factors:

if Code start is same of Code End , this code will take the score, if not, candies value will be splitted by 2.

Example. 471 if Code Start = Code End, 471/2 if Code Start not equal to Code End.

How can I correlate the two tables in order to obtain this final result? Since the correlation between two tables should be done with 2 columns only.

Thank you very much

[Anonymous]

Follow up

[Anonymous]

Hi @Anonymous,

Can you please share some sample data for test? 

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Dear @Anonymous,

thank you for your reply!! Really appreciate and sorry if I wasn't so clear, I will try to explain what I would do.

I have 2 tables. "Names" (list of operators) with the correlated codes for each of them and "Main" with all the records.

As per pic 1 below, let's take for example Row number 1. Job number "110" Started by code "DI-003" and ended by code "DI-003".

In this case, only one operator worked for this job.

Now let's take for example row number 3: Job number "120" Started by code "DI-004" and ended by code "DI-002".

In this case instead, two operators worked for this job.

 pic 1

 Basically now, what I want to is: for the case the same operator is starting and ending the job he will take the entire amount of weight, instead, in case the operators is starting and ending are differents the weight will be broken down in 2 parts.

So row number 1: DI-003 will take 150 Kg , row number 3: DI-004 will take 243 Kg and DI-002 as well 243 Kg.

The main issue here is that the correlation between the Table "MAIN" and "NAME" explained as per below picture.

Of course I can connect in relationship one of the two columns and not both. How can I bypass it?

Thank you very much for your support!

Marcex

[Anonymous]

HI @Anonymous,

You can transform your main table to re-group records:

Full query:

let
    Source = Main,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Job Number", "Start Code", "End Code", "Weight"}, "Type", "Date"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Job Number", "Weight", "Type", "Date"}, "Type2", "Code"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each Text.Start([Type],4) = Text.Start([Type2],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Type2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," Date","",Replacer.ReplaceText,{"Type"})
in
    #"Replaced Value"

Notice: this query reference from main table.

After these steps, you can use code and job fields to link two original tables.

BTW, I try to use dax formula to dynamic calculate out detail weight of each code but failed, it seems like power bi not support to recursively calculate based on dynamically previous result table.

Regards,

Xiaoxin Sheng