• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.

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.

67818

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

Sample Data Screenshot:

Address data:

67819

Negative Area:

67820
 

Attachments

  • NegativveAreaAnalysis.xlsx
    14.9 KB · Views: 2
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
 

Attachments

  • Negative Area lookup Check.xlsx
    169.8 KB · Views: 1
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.
Code:
=
FIRSTNONBLANK (
    FILTER (
        VALUES ( NegativeArea[Address] ),
        SEARCH ( NegativeArea[Address], Addressdata[Address], 1, 0 )
    ),
    1
)

67841
 
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.
 
... 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.
 
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
 

Attachments

  • 1589521990857_Negative Area lookup Check.xlsx
    170.2 KB · Views: 1
  • 1589522351668_NegativeAreaMatchingissue.jpg
    1589522351668_NegativeAreaMatchingissue.jpg
    34.9 KB · Views: 1
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.
 
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.
 

Attachments

  • NegativeAreaAnaly.xlsx
    12.4 KB · Views: 2
Sorry, am bit busy at work with projects. Haven’t had time to look at this. Maybe some time next week.
 
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:
Code:
= 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:
Code:
= 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.
 

Attachments

  • 1589521990857_Negative Area lookup Check.xlsx
    191.3 KB · Views: 3
Back
Top