Forum Discussion
Helper IIIDAX function to replicate Excel Index Match and Count if Function
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])))
Super UserHi,
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?
Kumshan450Hi,
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