Matching the Keyword values in the address field with the Negative area list

[Kumar Shanmugam]

Trying to replicate the match find value as mentioned in Rob Collie Blog: https://powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

I have a address data. I want to find whether certain keyword in address field matches with the list mentioned in negative Area. I applied below dax calculated column but it did not work.

NegativeArea = FIRSTNONBLANK(

filter(

VALUES(NegativeArea[Address]),

SEARCH(

Addressdata[Address],

NegativeArea[Address],

1,

0

)

)

,1

)

Additional Requirement: Before performing match of Keyword address with the address field in negative Area, I want the above calculated column formula to filter branch.

For eg: 1st row value.



For 1st row value, formula should filter branch Pune and then check for the matching Keyword values in address field with the address field in Negative Area.



Please find attached the sample data and also refer the screenshot below:

Sample Data Screenshot:

Address data:



Negative Area:

 

[Kumar Shanmugam]

Trying to replicate the match find value as mentioned in Rob Collie Blog: https://powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

I have a address data. I want to find whether certain keyword in address field matches with the list mentioned in negative Area. I applied below dax calculated column but it did not work.

NegativeArea = FIRSTNONBLANK(

filter(

VALUES(NegativeArea[Address]),

SEARCH(

Addressdata[Address],

NegativeArea[Address],

1,

0

)

)

,1

)

Additional Requirement: Before performing match of Keyword address with the address field in negative Area, I want the above calculated column formula to filter branch.

For eg: 1st row value.

View attachment 67817

For 1st row value, formula should filter branch Pune and then check for the matching Keyword values in address field with the address field in Negative Area.

View attachment 67818

Please find attached the sample data and also refer the screenshot below:

Sample Data Screenshot:

Address data:

View attachment 67819

Negative Area:

View attachment 67820

 

[Chihiro]

Personally, I'd not do it using DAX. I'd just use M Query. Since values do not change based on filter context.

But if you must... you'd do it like below.

=
FIRSTNONBLANK (
    FILTER (
        VALUES ( NegativeArea[Address] ),
        SEARCH ( NegativeArea[Address], Addressdata[Address], 1, 0 )
    ),
    1
)

 

[Kumar Shanmugam]

Thank u Chihiro very much. Is it possible to do using power query? Will it be faster then dax

 

[Kumar Shanmugam]

Also Before performing match of Keyword address with the address field in negative Area, I want the above calculated column formula to filter branch. Because there are probability that "ABC Nagar" will also reflect in Rajasthan where the mentioned negative area not in Rajasthan state but in Maharashtra.

 

[Chihiro]

... there are probability that "ABC Nagar" will also reflect in Rajasthan ...

I'd recommend uploading sample that is representative of your actual data.

As for PQ vs DAX, it's not so much which is faster, but where the evaluation takes place.

PQ will evaluate once, before data is loaded to data model. And will not re-evaluate once data is loaded (i.e. refreshed). Where as DAX will re-evaluate with each interaction with Pivot Table / Model.

Meaning, PQ may slow down initial load, but DAX will slow down context re-evaluation. Which is better, will depend on use case. But typically, for Lookup type operation, it's better to do it in PQ. Especially when values evaluated, will not change based on filter context etc.

 

[Kumar Shanmugam]

I'd recommend uploading sample that is representative of your actual data.

As for PQ vs DAX, it's not so much which is faster, but where the evaluation takes place.

PQ will evaluate once, before data is loaded to data model. And will not re-evaluate once data is loaded (i.e. refreshed). Where as DAX will re-evaluate with each interaction with Pivot Table / Model.

Meaning, PQ may slow down initial load, but DAX will slow down context re-evaluation. Which is better, will depend on use case. But typically, for Lookup type operation, it's better to do it in PQ. Especially when values evaluated, will not change based on filter context etc.

Hi please find attached the sample data

 

[Kumar Shanmugam]

Hi I am able to solve the partial issue using countif match formula : =IFERROR(INDEX(Sheet2!$C$2:$C$9,MATCH(1,COUNTIF(D2,"*? "&Sheet2!$C$2:$C$9&"*?"&Sheet2!$D$2:$D$9),0),0),""). Please find attached the sample file.

The above formula is able to find word as a word which is not embedded withn Another word. Problem solved on this part but this is in excel file.

Just wanted to know is it possible to replicate the same using dax.

 

[Kumar Shanmugam]

Hi I am able to solve the partial issue using countif match formula : =IFERROR(INDEX(Sheet2!$C$2:$C$9,MATCH(1,COUNTIF(D2,"*? "&Sheet2!$C$2:$C$9&"*?"&Sheet2!$D$2:$D$9),0),0),""). Please find attached the sample file.

The above formula is able to find word as a word which is not embedded withn Another word. Problem solved on this part but this is in excel file.

Just wanted to know is it possible to replicate the same using dax.

 

[Chihiro]

Sorry, am bit busy at work with projects. Haven’t had time to look at this. Maybe some time next week.

 

[Kumar Shanmugam]

No issues

 

[Chihiro]

Finally had some time to look at your file.

For process using PQ.

Merge NegativeArea to Addressdata, using Left Join and using State & Branch columns as key.

Then expand Address column of merged column.

Add custom column with following:

= try if Text.Contains([Address],[Address.1], Comparer.OrdinalIgnoreCase) then [Address.1] else null otherwise null

Sort by... Loan No ascending. Then by Custom column descending.
Add index column.
Add another custom column with following:

= try if #"Added Index"{[Index]-1}[Loan No] = [Loan No] then null else 1 otherwise 1

Filter for 1 in Custom.1.

Remove Address.1, Index & Custom.1 columns.

See attached sample.

 

[Kumar Shanmugam]

Thanks I will try the above method.