Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Use Measure to Count How Many Times Value Appears in Another Column

Hi All, I'm trying to see how often the values in one column appear in another column in a different table, and I need the count to change based on filters.  The below calculated column does the j...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    You could use this formula:

    Measure222 = COUNTROWS(FILTER('Table 1', 'Table 1'[Column A] in VALUES('Table 2'[Column A])))

     

    Regards,

    Lin

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Try like

calculate(COUNTROWS(FILTER(Table 1, Table 1[Column A] = firstnonbank(Table 2[Column A],blank())),values(Table 1[Column A] ))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak

I got an error that there were too many arguments for COUNTROWS, so I added an extra parenthesis after BLANK().  It's not returning anything.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like

    calculate(COUNTROWS(FILTER(Table 1, Table 1[Column A] = firstnonbank(Table 2[Column A],blank()))),values(Table 1[Column A] ))