Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Simple count but with multiple variables in the column

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...
General Comment
Need Help
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    Try this MEASURE

    Measure =
COUNTROWS (
    FILTER (
        Table2,
        SEARCH ( SELECTEDVALUE ( Table1[Countries] ), [Countries For], 1, 0 ) > 0
    )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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?