Calculated column on a table based on two columns from a different table

[Anonymous]

Hi Gurus, 

I have  requrirment that I struggel,

I have two tabels (Table 1 and table 2) I want a new calculated column in tabel 2.

logic is as below

if ColA and ColB (Tabel1) matches with Col1 and Col2 (Tabel2)   new column should say "Scheduel" if not it should say "New"

Below is a image of the tabels 

[Ashish_Mathur]

Hi,

Write this calculated column formula in Table2

=if(calculate(countrows(table2),filter(table1,table1[ColA]=earlier(table2[Col1])&&table1[ColB]=earlier(table2[Col2])))>0,"Schedule","New")

Hope this helps.

[Anonymous]

It does  not work

[amitchandak]

@Anonymous , New column in Table 2

new column =
Var _1 = countx(filter(Table1,Table1[ColA]=Table2[Col1] && Table1[ColB]=Table2[Col2]),[Table2[Col2]])
return if(isblank(_1) , "New","Scheduled")

[Anonymous · Accepted solution]

@amitchandak , 

it works, thanks a lot chapm. 

would you be able to explain the DAX machnics behind it. why would you use COUNTX, I thouugh it could get done with a LOOKUPVALUE function mearging with AND. (I tried it and failed).

[amitchandak]

@Anonymous , LOOKUPVALUE  shhould also work. But if there more then one values returned you need to handle that. In that one X function is bascially enfocing aggregation. So you will get one value. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.