Lookup a table after filtering certain values in the table

[Antmkjr]

Country	Sales	Rank	Continent	Sl
India	100	3	Asia	1
Pakistan	50	3	Asia	2
France	95	1	Europe	4
China	90	3	Asia	3
India	89	4	NULL	1

I have a Table A as shown above, and Table B as below:

Country	Sl	Sales
India	1	#ERROR
Pakistan	2	#ERROR
China	3	#ERROR

Im looking up Sales from Table A in Table B, However I want to lookup Table A excluding Continent = NULL,

How to frame the DAX for lookup by excluding the mentioned row from table A.

[v-cazheng-msft]

Hi @Antmkjr,

You need make some changes to your Calculated column formula.

LookUpSales =
SUMX (
    FILTER (
        'Table A',
        'Table A'[Country] = 'Table B'[Country]
            && 'Table A'[Continent] <> "NULL"
            && 'Table A'[Sl] = 'Table B'[Sl]
    ),
    'Table A'[Sales]
)

Then, the result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Antmkjr]

In this case its fine, but what if the value i have to return is text

[v-cazheng-msft · Accepted solution]

Hi @Antmkjr,

You could change the data type of the Measure by Column tools.

Or make a little bit change to the Measure itself.

LookUpSales =
var res=SUMX (
    FILTER (
        'Table A',
        'Table A'[Country] = 'Table B'[Country]
            && 'Table A'[Continent] <> "NULL"
            && 'Table A'[Sl] = 'Table B'[Sl]
    ),
    'Table A'[Sales]
)
return CONVERT(res,STRING)

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[amitchandak]

@Antmkjr , a New column in Table B

Sumx(filter( 'Table A', 'Table A'[Country] = 'Table b'[Country] ), 'Table A'[Sales])

or

Sumx(filter( 'Table A', 'Table A'[Country] = 'Table b'[Country] && 'Table A'[sl] = 'Table b'[sl] ), 'Table A'[Sales])

[Antmkjr]

In this case of both of these its Summing up the sales of India where Continent = NULL , i want to completely ignore that record