How to compare two column with the same name with the different table in power bi.

[arvindyadav]

Hi Team,

Need help regarding how I can compare two tables having the same column name. 

For example

Table 1- Last 3 days		Table 2- yesterdays
Name	Rpc		Name	Rpc
Arvind	1		Arvind	2.5
Yadav	0.23		soumyadeep	2
Pushkal	0.5		Pushkal	2.3
Soumyadeep	0.45		Ashish	1.5

So I need collated form of a new table which provides me Table name Result i.e, In last 3 days and Yesterdays table which name exist in both tables which one is moved out and which is New coming into the pictures.

I need output as

Needed Table(From Table-1 & Table -2)
Name	Output
Arvind	Exist
Yadav	Move Out
Pushkal	Exist
Soumyadeep	Exist
Ashish	New

Thanks,

Arvind

[TomMartens]

Hey,

I created a new table "New Table" using this DAX statement:

New Table = 
DISTINCT(
    UNION(
        VALUES(Table1[Name])
        ,VALUES('Table2'[Name])
    )
)

Then I create a calculated column using this DAX statement:

Output = 
var thisName = 'New Table'[Name]
var tExist = INTERSECT(VALUES('Table1'[Name]), VALUES('Table2'[Name]))
var tMoveOut = EXCEPT(VALUES('Table1'[Name]), VALUES('Table2'[Name]))
var tNew = EXCEPT(VALUES('Table2'[Name]), VALUES('Table1'[Name]))
return
SWITCH(
    TRUE()
    ,thisName in tExist, "Exist"
    ,thisName in tMoveOut, "Move Out"
    ,thisName in tNew, "New"
    ,"this is odd"
)

After this the table New Table looks like this:

Hopefully this is what you are looking for.

Regards,

Tom

[arvindyadav]

Hi @TomMartens ,

Thank You Very Much!!

It works but when I adding Table1 or table 2 RPC value then it reflects the same the number for all Name. How to fix this problem.

Thanks,

Arvind

[v-lili6-msft]

hi, @arvindyadav 

You just need to create the relationship between table1 and new table by Name column and create the relationship between table2 and new table by Name column too.

Best Regards,

Lin

[arvindyadav]

Hi @v-lili6-msft ,

How to create a relationship between the table.

I tried but nothing happens.

Thanks,

Arvind

[v-lili6-msft]

hi, @arvindyadav 

you could change Cross filter direction is "Both" and try it again.

If still have the issue, could you please share your sample pbix file, there should be something wrong in other.

Best Regards,

Lin

[arvindyadav]

Hi @v-lili6-msft ,

So basically I able to manage table1(Yesterdays) and Table2(Last 3 Days) tables relationship but when I try to relate New Table to any of the tables then I am getting a message like below:-

[TomMartens · Accepted solution]

Hey @arvindyadav 

from my understanding the relationships of the model should look like this:

This will allow to create a report like this:

Please be aware that the columns Name and Output are used from the table New Table. The table "New Table" to filter both tables. I think it will a good idea to rename the column RPC accordingly to make it somewhat simpler to differentiate between both columns, eg. "RPC (...) Yesterday". Make sure that you use the "Name" column from the table "New Table". 

Hopefully this is providing what you are looking for. 

Regards,

Tom

[arvindyadav]

Hi @TomMartens ,

Thank You So Much.

It's work for me.

Thanks,

Arvind