Fairly new to Dax - Count Question

[Anonymous]

Hello,

I am fairly new to Dax and find myself stuck.  I have data as below in the table with a Case Number where there are individual Tasks associated with that Case.  I need to do a couple of things, but believe they are all related in the Dax necessary to get my results.

I would like to create a new Column and count the number of times a Case Number has a Task = 1 and the Group = Team 1.  I'm able to get a result for that specific row of 1, but would like that result of 1 to appear on the rows having Task = 2 or 3 or beyond.

When I use:

My Team = CALCULATE(

COUNTROWS('Table Name'),'Table Name'[Case Number],'Table Name'[Group]="Team 1")

I get a 1 in the row where the Group = "Team 1".  However, the 4 rows below with the same Case Number (= 100) are blank and I would like to have the result return 1 on those rows as well.

Thank you.

[Anonymous]

Yes, thank you!  Looks like I need to read up on ALLEXCEPT. 

Appreciate the feedback and help!

[ERD · Accepted solution]

Hi @Anonymous ,

You can try this:

My Team =
CALCULATE (
    COUNTROWS ( 'Table Name' ),
    'Table Name'[Task] = 1,
    'Table Name'[Group] = "Team 1",
    ALLEXCEPT('Table Name','Table Name'[Case Number])
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.