Count value occurences of a column group by the ID

[Gorloot]

Hi, 

Please see below for example,

I would like to create a measure that counts how many times equipment has a certain system fixed. 

The count can become larger/smaller by the date filter.  The counting should not be affected by the Work order Nbr.

The purpose is to see how many times that the same system repaired appears by equipment ID in a date range.

Thank you.

Equipment ID	Work order Nbr	WO date created	System repaired	expected output : Count of system repaired
1	12345	2/9/2024	Tire	1
2	12346	2/10/2024	air condition	3
2	12333	1/10/2024	air condition	3
2	12386	4/10/2024	air condition	3
2	12346	2/10/2024	plug	2
2	12355	3/10/2024	plug	2
3	12347	2/10/2024	light	1
3	12347	2/10/2024	window	1
4	12348	2/13/2024	window	2
4	12348	2/13/2024	window	2
4	12348	2/13/2024	Tire	1
5	12349	2/15/2024	Engine	3
5	12349	2/15/2024	Engine	3
5	12350	2/16/2024	Engine	3

[Anonymous]

Hi @Gorloot 

Please try the following measure:

Count of System Repaired = 
CALCULATE(
    COUNTA('Table'[System repaired]), 
    ALLEXCEPT('Table', 'Table'[Equipment ID],'Table'[System repaired]),
    ALL('Table'[Work order Nbr])
)

Result:

Best Regards,

Jayleny

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

[Gorloot]

Hi @Anonymous ,

Thanks for quick response. I think this is very close to the solution.

However, I do have a bigger dataset and the measure is having a problem of grabbing other "System Repaired" counts from outside  "WO created" date range. 

I wish to grab the counts that are only limited to desired date range. 

Thank you again.

[Anonymous · Accepted solution]

Hi @Gorloot 

Try the following measure:

Count of System Repaired = 
VAR _mindate = MIN('Table'[WO date created])
VAR _maxdate = MAX('Table'[WO date created])
RETURN
CALCULATE(
    COUNTA('Table'[System repaired]), 
    FILTER(ALLEXCEPT('Table', 'Table'[Equipment ID],'Table'[System repaired]), SELECTEDVALUE('Table'[WO date created])>= _mindate && SELECTEDVALUE('Table'[WO date created])<= _maxdate)
)

Best Regards,

Jayleny

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