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.

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

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's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
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])
))