Forum Discussion

GSTI08's avatar
GSTI08
Frequent Visitor
5 years ago
Solved

DAX function to search for multiple values in strings and count the number of times each value occur

Hoping someone can help, I am new to DAX.   I am trying to count the number of times specific texts occurs.   I have a table/Column with multiple values in it.  Regions Europe, ME, UKI ...
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi GSTI08 ,

     

    It is suggested to create another region table by DAX or just enter data.

    Region =
DATATABLE (
    "Region", STRING,
    {
        { "Africa" },
        { "South America" },
        { "ME" },
        { "North America" },
        { "UKI" },
        { "Europe" }
    }
)

     

    Then, create measures like what Greg_Deckler provided.

    4 CalcRegion = 
VAR __SearchTerms =
    ADDCOLUMNS (
        Regions,
        "Count",
            COUNTROWS (
                FILTER (
                    'Accounts',
                    FIND ( [Region], 'Accounts'[Primary Connectivity Regions],, 0 ) > 0
                )
            )
    )
RETURN
    SUMX ( __SearchTerms, [Count] )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

GSTI08 Your DAX formula could be greatly simplified:

4 CalcRegion =
  VAR __SearchTerms = 
    ADDCOLUMNS(
      { "Africa", "South America", "ME", "North America", "UKI", "Europe" },
      "Count", COUNTROWS(FILTER('Accounts',FIND([Value],'Accounts'[Primary Connectivity Regions],,0)>0))
RETURN
  SUMX(__SearchTerms,[Count])
GSTI08's avatar
GSTI08
Frequent Visitor
5 years ago

Hi Greg, 

 

That looks much better, thank you.  Although I am getting a "The Syntax for 'RETURN' is incorrect, but I can't see why, it looks fine.  Any ideas?

 

Thanks

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    GSTI08 


    Add a closing bracket ")" to Greg_Deckler 's formula before the RETURN as below.

     

    4 CalcRegion =
  VAR __SearchTerms = 
    ADDCOLUMNS(
      { "Africa", "South America", "ME", "North America", "UKI", "Europe" },
      "Count", COUNTROWS(FILTER('Accounts',FIND([Value],'Accounts'[Primary Connectivity Regions],,0)>0))
    )
RETURN
  SUMX(__SearchTerms,[Count])

     

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn