Count in DAx and exclude data based on criteria

[Mark_Berry12]

Hello everyone,

I am required to provide a count based on which stakeholders own them.
However there are some Packages which are owned by two stakeholders.

I need to count the number of packages which are held exclusivley by stakeholder and present it as a Card. 

So using the data below I would need to see the Packages which are held only by Stakeholder A and not count any packages that are also held by Stakeholder B,C or D. 

So whilst A has a total 12 packages I would want to return 3 being the only packages that are exclusive to them.

Stakeholder	Package
A	1001
B	1001
C	1002
A	3001
A	3002
B	3004
A	3004
B	3005
A	3005
B	3007
A	3008
B	3008
B	3010
B	3010
A	3011
B	3013
A	3013
B	3014
A	3014
B	3016
D	3016
B	3016
D	3016
A	3017
B	3017
A	3018
B	3018
A	3019
B	3019
B	3020

[rajendraongole1 · Accepted solution]

Hi @Mark_Berry12  - you can  create below calculated table and measure as follows:

Create a new calculated table :

ExclusivePackages =

VAR UniquePackages =

    ADDCOLUMNS (

        SUMMARIZE ( 'Stapack', 'Stapack'[Package] ),

        "StakeholderCount", COUNTROWS ( FILTER ( 'Stapack', 'Stapack'[Package] = EARLIER ( 'Stapack'[Package] ) ) )

    )

RETURN

    FILTER ( UniquePackages, [StakeholderCount] = 1 )

Create a measure as below:

ExclusivePackageCountA =

VAR PackagesWithOneOwner =

    FILTER (

        SUMMARIZE ( 'stapack', 'stapack'[Package] ),

        CALCULATE ( DISTINCTCOUNT ( 'stapack'[Stakeholder] ) ) = 1

    )

RETURN

    COUNTROWS (

        FILTER ( PackagesWithOneOwner,

            CALCULATE ( MAX ( 'stapack'[Stakeholder] ) ) = "A"

        )

    )

Hope this helps.