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.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

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'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])
      ))