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
UKI
South America, Africa
North America, UKI, Africa, Europe
Africa

 

I want to be able to count how many times a specfic value occurs so the output I am looking for in a visual would be

 

RegionCount
Africa3
South America1
ME1
North America1
UKI3
Europe2

 

I would prefer to do this in DAX rather than tables or query editor if this is possible.  

 

I did try to do this with a nested IF, but that sin't working and just brings me back true or false. 

4 CalcRegion =
IF(CALCULATE(COUNTROWS(Accounts),FILTER(Accounts,CONTAINSSTRING(Accounts[Primary Connectivity Regions],"Europe")))>0,1,0)
+IF(CALCULATE(COUNTROWS(Accounts),FILTER(Accounts,CONTAINSSTRING(Accounts[Primary Connectivity Regions],"Far East")))>0,1,0)
+IF(CALCULATE(COUNTROWS(Accounts),FILTER(Accounts,CONTAINSSTRING(Accounts[Primary Connectivity Regions],"UKI")))>0,1,0)
 
Any help or pointers in the right direction would be really appreciated.
 
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.

6 Replies

  • 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




  • Icey's avatar
    Icey
    Community Support
    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.

  • GSTI08's avatar
    GSTI08
    Frequent Visitor
    5 years ago

    Thank you very much everyone for you help, particulary Greg and Icey, 

     

    This is working perfectly!  

     

    Really appreciate everyone's effort.