Measure to count repeats

[ReadTheIron]

I have a table of equipment failures. I want to count the number of pieces of equipment that failed more than once in a given timeframe (driven by user/slider on page).

My data looks something like this:

EquipmentArea	EquipmentName	FailureDate
North	Switch1	1/2/2023
North	Switch2	1/2/2023
South	Switch3	1/5/2023
East	Switch4	1/10/2023
West	Switch5	1/11/2023
East	Switch4	1/15/2023
South	Switch3	1/15/2023
North	Switch1	1/20/2023
North	Switch2	1/21/2023
North	Switch2	1/22/2023

I want it to look like this:

EquipmentArea	RepeatFailures
North	2
South	1
East	1

I'm currently using the measure

RepeatCountEquipment = COUNT(Table1[EquipmentName]) - Distinctcount(Table1[EquipmentName])

But that gives me:

EquipmentArea	RepeatFailures
North	3
South	1
East	1

I don't care how many times something repeats, just that it repeats at all.

[ppm1 · Accepted solution]

Please try this measure expression. Replace Failures with your actual table name.

Equipment MT1 Failures =
VAR tSummary =
    SUMMARIZE (
        Failures,
        Failures[EquipmentName],
        "cCount", COUNT ( Failures[EquipmentName] )
    )
RETURN
    COUNTROWS ( FILTER ( tSummary, [cCount] > 1 ) )

Pat

[ReadTheIron]

Perfect! Thank you so much!

[Padycosmos]

Hope this helps:

[ReadTheIron]

This works on the sample data but not on my full set - I'm trying to figure out why! Is there a way of doing this without reference to the equipment area?

[grantsamborn]

Hi @ReadTheIron 

Does this help?

Repeats TrueFalse = 
VAR _count = COUNT( Table1[EquipmentName] )
VAR _distinct = DISTINCTCOUNT( Table1[EquipmentName] )
VAR _temp = _count - _distinct
VAR _result =
    IF(
        _temp > 0,
        TRUE(),
        FALSE()
    )
RETURN
    _result

[ReadTheIron]

That work well for telling me whether an individual piece of equipment failed >1 or not, but I can't seem to use it as a filter telling me how many pieces of equipment in an area failed >1.