Simple count but with multiple variables in the column

[Anonymous]

Hey guys so my issue is that I have a table of lets say 3 counties A, B and C. And another table that has a row a column of "Contries for" but in this table you could have more than one contry listed (no reppition) and delimited by a comma. As shown below:

(Did it on excell just to show what I mean)

So what I did was use a simple count measure on "Countries For"  and when I try to tabulate it, it is just taking the rows with multiple countries as "Other" as represented by the blank row in the results I get. My question is how can I make it count every country in the coulumn. 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        Table2,
        SEARCH ( SELECTEDVALUE ( Table1[Countries] ), [Countries For], 1, 0 ) > 0
    )
)

[Anonymous]

@Zubair_Muhammad 
 Thanks for your quick reply but I am still getting the same result, using your formula. 

[Anonymous]

@Zubair_Muhammad 
After playing around with my relationships it seems your answer is correct iff my two tables are unlinked in my relationship model. However if they are linked I get the same problem. Could you help explain why this is so? As I do not want to create a whole new unlinked redundant table just for this calculation.

[Zubair_Muhammad]

@Anonymous 

If they are linked somehow, i believe you can use ALL function to remove the FILTER context

Measure =
COUNTROWS (
    FILTER (
        ALL(Table2),
        SEARCH ( SELECTEDVALUE ( Table1[Countries] ), [Countries For], 1, 0 ) > 0
    )
)

[Anonymous]

@Zubair_Muhammad 
Yes worked perfectly!

I am trying to wrap my head around the thought process of this function, if you coud describe how it works would be much appreciated!

[Zubair_Muhammad]

@Anonymous 

This formula is just filtering Table 2 where the string/text (selectedvalue of Table1[Countires]) is found

Then it Counts the rows of filtered Table2

so for the first Table Visual calculation, it works like this

Filter(Table2 , Where Country A  is found in column "Countries For" of Table2)

then

Countrows of above filtered table

[Anonymous]

@Zubair_Muhammad  @Mariusz 
I realised that the numbers are perfectly correct for each country, but when it does the autosum at the bottom of the table this value is incorrect. Would you know the reason why this is so? 
I believe it only sums the number of rows in the original table instead of summing the values of the produced table. Would there be a workaround for this?

[Zubair_Muhammad]

@Anonymous 

Try this MEASURE

Measure =
IF (
    HASONEFILTER ( Table1[Countries] ),
    COUNTROWS (
        FILTER (
            Table2,
            SEARCH ( SELECTEDVALUE ( Table1[Countries] ), [Countries For], 1, 0 ) > 0
        )
    ),
    SUMX (
        VALUES ( Table1[Countries] ),
        CALCULATE (
            COUNTROWS (
                FILTER (
                    Table2,
                    SEARCH ( SELECTEDVALUE ( Table1[Countries] ), [Countries For], 1, 0 ) > 0
                )
            )
        )
    )
)

[Zubair_Muhammad]

@Anonymous 

Please check attached file with your sample data