Forum Discussion

Kumshan450's avatar
Kumshan450
Icon for Helper III rankHelper III
6 years ago
Solved

DAX function to replicate Excel Index Match and Count if Function

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:...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    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's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

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's avatar
Kumshan450
Icon for Helper III rankHelper III
6 years ago

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 NoStateBranchAddressNegativeArea
655RajasthanJaipurGali No 5 Janta Dal Colony Vishal Nagar Vishal Market Jaipur 511202Vishal Nagar
329MaharashtraPuneSector 08 Vishal Nagar Vishal Market Jaipur 411252