CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
MNPOWER
MNPOWER

Reputation: 1

Calculated Column --A Table of Multiple Values was supplied using DAX IN POWERBI -

I have two tables which have two columns some with similar data but I want lookup values from one of the table when the data in the two columns match and if it does not match it returns data in the master column

TABLE A

Company_Code  |  Invoice_No | Buyer_Code| Diaspora_Buyer_Code

A|1|001
A|6|002
B|2|003
C|3|001
D|5|006

TABLE B

Company_Code | Invoice_No|Diaspora_Buyer_Code

A|1|11
A|6|12
B|2|11

EXPECTED RESULT

Table A should have the below values in the Diaspora_Buyer_Code(Calculated Column)

TABLE A

Company_Code  |  Invoice_No | Buyer_Code| Diaspora_Buyer_Code

A|1|001|11
A|6|002|12
B|2|003|11
C|3|001|001
D|5|006|006

Upvotes: 0

Views: 3270

Answers (2)

Shashiraju
Shashiraju

Reputation: 149

Try this

Diaspora_Buyer_Code = 
        IF(ISBLANK(CALCULATE(FIRSTNONBLANK('Table B'[Diaspora_Buyer_Code],'Table B'[Diaspora_Buyer_Code]), 
                            FILTER('Table B','Table A'[  Invoice_No ]='Table B'[ Invoice_No]),
                            FILTER('Table B', 'Table B'[Company_Code ]='Table A'[Company_Code  ]))),
            VALUE('Table A'[ Buyer_Code]),
            CALCULATE(FIRSTNONBLANK('Table B'[Diaspora_Buyer_Code],'Table B'[Diaspora_Buyer_Code]), 
                    FILTER('Table B','Table A'[  Invoice_No ]='Table B'[ Invoice_No]),
                    FILTER('Table B', 'Table B'[Company_Code ]='Table A'[Company_Code  ])))

Upvotes: 1

Pratik Bhavsar
Pratik Bhavsar

Reputation: 858

Add calculated columns in both tables:

Table A

 KeyColumn = 'Table A'[Company_Code]&"-"& 'Table A'[Invoice_No]

Table B

KeyColumn = 'Table B'[Company_Code]&"-"& 'Table B'[Invoice_No]

Method 1

Create a relationship between both key columns. Now the formula for expected calculated column goes as follows: 

Diaspora_Buyer_Code = 
VAR RelatedVal = 
    RELATED('Table B'[Diaspora_Buyer_Code])

RETURN 
    IF (
        ISBLANK(RelatedVal),
        'Table A'[Buyer_Code],
        RelatedVal
    )

If you do not want to create the relation then use the following formula for the column: 

Diaspora_Buyer_Code = 
VAR Lookup = 
    LOOKUPVALUE(
        'Table B'[Diaspora_Buyer_Code], 
        'Table B'[KeyColumn], 
        'Table A'[KeyColumn]
    )

RETURN 
    IF (
        ISBLANK(Lookup),
        'Table A'[Buyer_Code],
        Lookup
    )

Output in both cases:

enter image description here

Upvotes: 0

Related Questions