DAX function to replicate Excel Index Match and Count if Function

[Kumshan450]

I have address data. I want to find whether certain keyword in address field matches with the list mentioned in the negative Area. 

I applied below formula in excel file: =IFERROR(INDEX(Sheet2!$C$2:$C$9,MATCH(1,COUNTIF(D2,"*? "&Sheet2!$C$2:$C$9&"*?"&Sheet2!$D$2:$D$9),0),0),"").

In excel file it takes time as there are more than 7 million rows of data.

So my perception is that DAX formula would be faster and formula would be automated. I have applied the dax formula but not getting the correct result.  Please find herewith the link for the sample file. 

Excel File

https://drive.google.com/file/d/14MbimcuQOsCb7I1FVBXW_NEKS_rpoWDr/view?usp=sharing

Power BI file

https://drive.google.com/file/d/1g_dfN-qm9G_D7WD3OQv_q2hnQingzPc-/view?usp=sharing

If you check the results of the Negative Area column in the excel file and Power BI file, the result of the excel file is correct. 

Is there any method to speed up the lookup operation in dax so that it does not take more time in the evaluation. 

[AllisonKennedy · Accepted solution]

Your DAX attempt is missing the PINCODE part. 

I think this should work, but not sure it's the most efficient solution: 

NegativeArea = CALCULATE(

FIRSTNONBLANK(NegativeArea[Address],NegativeArea[Address]),

FILTER (

NegativeArea,

CONTAINSSTRING(AddressData[Address],NegativeArea[Address]) && CONTAINSSTRING(AddressData[Address], NegativeArea[PINCode])

))

[Ashish_Mathur]

Hi,

Why are we searching for columns C and D appearing in the NeagtiveArea worksheet in column D of the Addressdata worksheet?  Why not search only for column C appearing in the NeagtiveArea worksheet in column D of the Addressdata worksheet?  Wouldn't that be enough?

[Kumshan450]

Hi,

The logic is area+Pin code both should match. 

For Eg, if you check Vishal Nagar as Negative Area. 

In the address data worksheet, there are two loan accounts where the address contains Vishal Nagar. If I apply only by column c then the second loan will also get matched up which I don't want as PIN code is different. So PIN code is mapping is required in the formula. 

Loan No	State	Branch	Address	NegativeArea
655	Rajasthan	Jaipur	Gali No 5 Janta Dal Colony Vishal Nagar Vishal Market Jaipur 511202	Vishal Nagar
329	Maharashtra	Pune	Sector 08 Vishal Nagar Vishal Market Jaipur 411252

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AllisonKennedy]

Can you explain the goal in business sense, not just Excel formula? Looks like your DAX formula doesn't currently account for the PIN, does the Address[Address] always have the 6 digit PIN at the end? If so you can split those columns or use that to get the right answer eventually. Lookup, search, etc are still not exactly efficient in DAX, so if you can describe further the why and the goal the community might have some better solutions for you. 

[Kumshan450]

The goal is to find whether my address data contains any Negative area with PINcode Keyword. This is used for analyzing total business and the number of loans booked in negative areas. 

Yes, all addresses end with PIN code. 

[AllisonKennedy · Accepted solution]

Your DAX attempt is missing the PINCODE part. 

I think this should work, but not sure it's the most efficient solution: 

NegativeArea = CALCULATE(

FIRSTNONBLANK(NegativeArea[Address],NegativeArea[Address]),

FILTER (

NegativeArea,

CONTAINSSTRING(AddressData[Address],NegativeArea[Address]) && CONTAINSSTRING(AddressData[Address], NegativeArea[PINCode])

))