DAX 'CONTAINSX' type formula

[dpotta]

Hi

I have two unrelated tables. postcodes is a complete list of UK postcodes and sf is a table of opportunity names. I have been using the formula below to search for a postcode within the column [opportunity name] in the sf table and return Yes/No.


Postcode = IF(
SUMX(postcodes,
FIND(
UPPER(postcodes[Postcode]),
UPPER(sf[Opportunity Name])
,,0
)
) > 0,
"Yes",
"Probably not"

What I would like to do is use a variation of the formula to return the actual postcode rather than "yes". I have tried using LOOKUPVALUE in there, but I still cannot work out how to do this with two unconnected tables. This is an example of the sf[opportunity name] column. The formula returns "Yes" in the two bold rows.

EMEA ? A company name ? Flat 12, 100 A road, London W1J 9NH ? Product
EMEA ? A company name ? 35-37 & 39 Molyneux Road, London GU& 3NJ? Other Products
EMEA - A company name - 29 Meanwhile Gardens, London - Q1 2016 Valuation - Different Products
EMEA - UK - Habro - Aberdeen - Jesmond Drive Part 22

Can anyone help?

Many thanks in advance

Dave

 

[dpotta]

Finding a UK Postcode in a text string with DAX

Hi

I have two unrelated tables. postcodes is a complete list of UK postcodes and sf is a table of opportunity names. I have been using the formula below to search for a postcode within the column [opportunity name] in the sf table and return Yes/No.


Postcode = IF(
SUMX(postcodes,
FIND(
UPPER(postcodes[Postcode]),
UPPER(sf[Opportunity Name])
,,0
)
) > 0,
"Yes",
"Probably not"

What I would like to do is use a variation of the formula to return the actual postcode rather than "yes". I have tried using LOOKUPVALUE in there, but I still cannot work out how to do this with two unconnected tables. This is an example of the sf[opportunity name] column. The formula returns "Yes" in the two bold rows.

EMEA ? A company name ? Flat 12, 100 A road, London W1J 9NH ? Product
EMEA ? A company name ? 35-37 & 39 Molyneux Road, London GU& 3NJ? Other Products
EMEA - A company name - 29 Meanwhile Gardens, London - Q1 2016 Valuation - Different Products
EMEA - UK - Habro - Aberdeen - Jesmond Drive Part 22

Can anyone help?

Many thanks in advance

Dave

Resolved! This works with the following formula:

=FIRSTNONBLANK(FILTER(
VALUES(Postcodes[postcode]),
SEARCH(
postcodes[postcode],
sf[Opportunity Name],
1,
0
)
)
,1
)