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

[GSTI08]

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

Region	Count
Africa	3
South America	1
ME	1
North America	1
UKI	3
Europe	2

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.

[GSTI08]

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

This is working perfectly!  

Really appreciate everyone's effort. 

[Icey · Accepted solution]

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]

@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]

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]

@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

[VijayP]

@GSTI08 

Its working for me 

Let me share the file here https://drive.google.com/file/d/1QUkqA8W4WjJfBRHOmwPXwGbV5noLbZcC/view?usp=sharing

Vijay Perepa

If this is the solution you are looking for mark this as solution and share your Kudoes