Count values within a groupby and filter

[annade22]

Hi,

I hope someone can help me. 

I want to count the Categories per ParentProject and Group if at least one Category = SA. I the ParentProject/Group doesn't include any SA, then I don't want to do anything.

My table look like this

ParentProject	ProjectId	Category	Group
1000	1000-1	SA	1
1000	1000-2	MB	1
1000	1000-3	PL	1
1000	1000-4	RO	2
1001	1001-1	SA	1
1001	1001-2	RO	2
1002	1002-1	SA	1
1003	1003-1	MB	1
1003	1003-2	PL	2
1004	1004-1	SA	1
1004	1004-2	MB	1
1004	1004-3	PL	1

My expected result is

ParentProject	Number of categories
1000	3
1001	1
1002	1
1003	0
1004	3

I assume I need a groupby, count and filter function but I can't get it right. Appreciate all help

[v-chuncz-msft · Accepted solution]

@annade22 

You may add the following measure.

Measure =
COUNTROWS (
    FILTER (
        Table1,
        CONTAINS ( Table1, Table1[Group], Table1[Group], Table1[Category], "SA" )
    )
) + 0

[AlB]

Hi @annade22 

Try this:

1. Place ParentProject and Group in the rows of a matrix visual. Another option is to place ParentProject only in the visual and Group on a slicer

2.  Create this measure and place it in the visual:

NumCategories =
CALCULATE ( DISTINCTCOUNT ( Table1[Category] ), Table1[Category] <> "SA" )

[annade22]

Thankyou, it works to some extent, but I want to filter within a parentproject. The parentprojects that don't have any value of "SA" should be excluded from the formula completely. 

Appreicate your help though!