Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ReadTheIron
Helper III
Helper III

Measure to count repeats

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:

EquipmentAreaEquipmentNameFailureDate
NorthSwitch11/2/2023
NorthSwitch21/2/2023
SouthSwitch31/5/2023
EastSwitch41/10/2023
WestSwitch51/11/2023
EastSwitch41/15/2023
SouthSwitch31/15/2023
NorthSwitch11/20/2023
NorthSwitch21/21/2023
NorthSwitch21/22/2023

 

I want it to look like this:

EquipmentAreaRepeatFailures
North2
South1
East1

 

I'm currently using the measure

RepeatCountEquipment = COUNT(Table1[EquipmentName]) - Distinctcount(Table1[EquipmentName])
 
But that gives me:
EquipmentAreaRepeatFailures
North3
South1
East1

 

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

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

View solution in original post

6 REPLIES 6
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

Perfect! Thank you so much!

Padycosmos
Solution Sage
Solution Sage

Hope this helps:

Padycosmos_0-1676324946730.png

 

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
Solution Sage
Solution Sage

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

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.