If clause from two different tables

[Nourhassan]

Hello,

I would like to filter a condition from table X and look if this record is also given in table Y.

Example

Table X:

Title	Approved
ABC-1	Yes
ABC-2	No
ABC-3	No

Table Y:

Title	Approved
ABC-4	No
ABC-5	No

So I need to look if ABC-1 is Approved="Yes" but not containing in table Y and retrieve 0. 

I am not sure how to write it in DAX and need your help, please.

Thank you in advance.

[Nourhassan]

Hello,

Sorry for not being so clear. I had a mistake with my description, so here again what I want:

I have two tables and I want to check if a record from the first table with the conditions that FirstApproval=Yes and Advanced=Yes are recorded in the Vouchers table (the second table). If yes, then retrieve me 0, and if no, then give me the Amount of the record from the first table. The conditions are only given as columns in table 1.

Petty Cash (Table 1):

Reference ID	FirstApproval	Advanced	Amount
20	Yes	Yes	100
21	Yes	No	110
22	Yes	Yes	120

Vouchers (Table2):

Title	Amount
21	110
22	120

So here we can see that the record with Reference ID 20 and the required conditions is not listed in the Vouchers table. The new column should retrieve the amount of this record from the Petty Cash table (table 1).

I hope this is now more clear.

Thank you.  

[ERD]

Hi @Nourhassan ,

Do you need a new column in your Petty Cash table or a measure ?

In case of a column:

NewAmt column = 
IF(
    'T-Petty Cash'[FirstApproval] = "Yes" 
        && 'T-Petty Cash'[Advanced] = "Yes" 
        && 'T-Petty Cash'[Reference ID] IN VALUES('T-Vouchers'[Title]), 
    0, 
    'T-Petty Cash'[Amount]
)

In case of a measure:

#NewAmt measure = 
SUMX (
    VALUES ( 'T-Petty Cash'[Reference ID] ),
    CALCULATE (
        IF (
            MAX ( 'T-Petty Cash'[FirstApproval] ) = "Yes"
                && MAX ( 'T-Petty Cash'[Advanced] ) = "Yes"
                && MAX ( 'T-Petty Cash'[Reference ID] ) IN VALUES ( 'T-Vouchers'[Title] ),
            0,
            MAX ( 'T-Petty Cash'[Amount] )
        )
    )
)

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

[amitchandak]

@Nourhassan , Not very clear.

But you need a new column like this in Table X

new column =
if(countx(filter(TableY, TableY[Title] = TableX[Title] && TableY[Approved] ="Yes"), TableY[Title]) +0>0, "Yes", "No")

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8