Count values associated with one category but not another

[RL1]

Appologies if this is a simple one, but I'm fairly new to PowerBI and haven't ben able to find an answer using search.

Basically I have a table similar to the following:

category     value

A                   X

A                   Y

A                   Z

B                   W

B                   X

C                   V

C                  W

and I want create charts showing counts of the values of A and C that are not associated with B.

i.e. in the above example I want to find Y and Z so get a count of 2 for A and to just count V when looking at C.

Thanks

[v-yulgu-msft]

Hi @RL1,

Does below output meet your requirement?

If yes, please refer to below steps.

Create two calculated tables.

Table For B = CALCULATETABLE('Count Values','Count Values'[Category]="B")

Table For A&C = CALCULATETABLE('Count Values', 'Count Values'[Category]<>"B")

In 'Table For A&C', add a calculated column using LookUpValue function.

Col1 =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'Table For B'[Value],
            'Table For B'[Value], 'Table For A&C'[Value]
        )
    ),
    1,
    BLANK ()
)

Create a measure to show the count of the values of A and C that are not associated with B.

Count for A&C =
CALCULATE (
    COUNT ( 'Table For A&C'[Col1] ),
    ALLEXCEPT ( 'Table For A&C', 'Table For A&C'[Category] )
)

Best regards,
Yuliana Gu

[Dog]

Hi @RL1

you probably just want to create a measure that performs the count of hte category column 

CountOfCategories :=
COUNT ( TableName[Category] )

Then create another Measure to filter on the Y and Z 

CountOfYZ :=
CALCULATE ( [CountOfCategories], [Value] = "Y" || [Value] = "Z" )

when you add the category and CountOfYZ to a chart each count should calculate for you. 

Hopefully that's what you are after. 

[RL1]

Thanks,

In reality I have thousands of different values so wouldn't want to specify all of them, is it simple to switch this around to specify the category to exclude matching values for rather than the values themselves?

[Dog]

is this any help? replace tablename with your tablename, 

CategoryNotInB :=
CALCULATE (
[CountOfCategories],
FILTER (
TableName,
NOT (
CONTAINS (
FILTER ( TableName, TableName[category] = "B" ),
TableName[Value], TableName[Value]))))