• 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

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

Chihiro

Excel Ninja
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.
 

Chihiro

Excel Ninja
... 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

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

Chihiro

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