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

0
votes

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
2
powerbidaxpowerbi-desktop
your going to need a join statement but stackoverflow is not meant to be a code writing service so you should try something first and if it doesn't work ask for help. Also please use the code tags in the editor to make your code more readable for everybody. – Tarick Welling
can you post a shot of your data model? Are these tables joined by a relationship? – StelioK

2 Answers

1
votes

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  ])))
0
votes

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