How can I filter the table by Category = Animal
, then group by Class, and count the distinct id for each group?
So far I can only group by and count distinct values:
output = SUMMARIZE(my_table, my_table[Class], "Distinct Count", DISTINCTCOUNT(my_table[Id]))
I tried:
output = CALCULATETABLE(SUMMARIZE(my_table, my_table[Class]), DISTINCTCOUNT(my_table[Id]), FILTER(my_table, my_table[Category ] = "Animal"))
which caught error:
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
I also tried the way suggested by this post but it only counts the number of rows in the table rather than the distinct number of Id
.