Forum Discussion
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 job for a static count:
=COUNTROWS(FILTER(Table 1, Table 1[Column A] = Table 2[Column A])))
Whenever I try to drop the formula into a measure, I get an error that a single value for the column cannot be determined. I'd appreciate any help anyone can give.
hi Anonymous
You could use this formula:
Measure222 = COUNTROWS(FILTER('Table 1', 'Table 1'[Column A] in VALUES('Table 2'[Column A])))Regards,
Lin
7 Replies
1)Create a new table for unique values in table 1:
Values Table 1 = VALUES(table1[column A])2) in the modeling pane, create a relationship between the new table and table 2 (if you need to do other calculations involving both tables, also create a relatiosnhip with table 1)
3) new measure
Counting Table 2 values = COUNTROWS(table 2)
4) create a table visual with column from “Values Table 1” and the measure [Counting Table 2 values]hi Anonymous
You could use this formula:
Measure222 = COUNTROWS(FILTER('Table 1', 'Table 1'[Column A] in VALUES('Table 2'[Column A])))Regards,
Lin
I attempted to use this, however it did not bring back the total amount of times the value appeard in the second table.
Try like
calculate(COUNTROWS(FILTER(Table 1, Table 1[Column A] = firstnonbank(Table 2[Column A],blank())),values(Table 1[Column A] ))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.Try like
calculate(COUNTROWS(FILTER(Table 1, Table 1[Column A] = firstnonbank(Table 2[Column A],blank()))),values(Table 1[Column A] ))
Anonymous
Or as an alternative (no need to create a new table) you can try:
Measure = CALCULATE(COUNTROWS(table 2), TREATAS(VALUES(table 1 [column A]), table 2 [column A]))and include the field "table 1 [column A]" in your table.
