Forum Discussion

Mark_Berry12's avatar
Mark_Berry12
Frequent Visitor
1 year ago
Solved

Count in DAx and exclude data based on criteria

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.

 

StakeholderPackage
A1001
B1001
C1002
A3001
A3002
B3004
A3004
B3005
A3005
B3007
A3008
B3008
B3010
B3010
A3011
B3013
A3013
B3014
A3014
B3016
D3016
B3016
D3016
A3017
B3017
A3018
B3018
A3019
B3019
B3020
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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.