IF condition for two different tables

[Baji_Komara]

Hi,

I have 2 tables conatining data of 6 months , I'm trying to apply the if condition for two different tables, Table A containig unique(not repeating) coustomer ID's and the Table B containing repeated coustomer id's that are repeating  only once in a month (refer the below screenshots for clarity). i want to create a new column in Table A by writing DAX IF code to get the purchase amount of the coustomer mapped to coustomer id in Table B. Table A and Table B mapped provided with relationship connecting claim numbers

Here is the DAX code i wanted to write to get the mapping

Method 1

Apri month= if('TableB'[financial_month] = April && 'TableA'[financial_month] = April , LOOKUPVALUE('TableB'[purchase_amount],'TableB'[coustomer_ID],'TableA'[coustomer_ID]))

May month = IF('TableB'[financial_month] = May && 'TableA'[financial_month] = May , LOOKUPVALUE('TableB'[purchase_amount],'TableB'[coustomer_ID],'TableA'[coustomer_ID]))

Method 2

April month = IF( Related (Table A [Financial_month]) = April ,LOOKUPVALUE('TableB'[purchase_amount],'TableB'[coustomer_ID],'TableA'[coustomer_ID]))

May month = if ( Related (Table A [Financial_month]) = May ,LOOKUPVALUE('TableB'[purchase_amount],'TableB'[coustomer_ID],'TableA'[coustomer_ID]))

 i want to write the above DAX codes for every month upto september

Since the coustomer ID's in Table B is repeating in every month i'm getting an error saying the data is repeated. when i tried to apply RELATED( ) in method2 after entering RELATED when i tried to enter the target column nothing is being suggested by PowerBI whatever i'm entering it is in lightgrey colour only and not changing to black

Please help me to resolve the issue and if possible suggest me a simpler way to get the data for every months without manually creating the columns

here claim_no = coustomer ID

Table A

Table B 

 

 

[v-yinliw-msft · Accepted solution]

Hi @Baji_Komara ,

You can try these methods:

Method1:

New columns like:

Apri month =
IF (
    MONTH ( 'TableA'[Financial Month] ) = 4,
    CALCULATE (
        SUM ( TableB[purchase_amount] ),
        FILTER (
            'TableB',
            MONTH ( 'TableB'[Financial Month] ) = 4
                && 'TableA'[CLAIM_NO] = 'TableB'[CLAIM_NO]
        )
    )
)

Method2: Use matrix visual.

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[Baji_Komara]

hi @v-yinliw-msft ,

Thanks for your response and the solution