0
votes

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.

2

2 Answers

1
votes

Try this:

output =
SUMMARIZECOLUMNS (
    my_table[Class],
    TREATAS ( { "Animal" }, my_table[Category] ),
    "Distinct Count", DISTINCTCOUNT ( my_table[Id] )
)

Another option:

output =
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( my_table[Class] ),
        "Distinct ID", CALCULATE ( DISTINCTCOUNT ( my_table[Id] ) )
    ),
    my_table[Category ] = "Animal"
)

SUMMARIZECOLUMNS version is generally an optimized way of writing the code so you should prefer that one. I have included ADDCOLUMNS/CALCULATETABLE construct only for the learning purpose.

1
votes

Check this out: Filter SUMMARIZECOLUMNS

You filter in this way:

VAR  __MyFilterTable = FILTER( ALL(T[category]), T[category] = "animal" ) 

RETURN
SUMMARIZECOLUMNS (
    T[category],
    __MyFilterTable
)

Or even shorter:

VAR  __MyFilterTable = TREATAS ({"animal"}, T[category] )

RETURN
SUMMARIZECOLUMNS (
    T[category],
    __MyFilterTable
)