display faulty devices

[jitpbi]

Hi,

the below is the sample of my dataset and the visual based on this dataset:

Datetime	Device	Faults
10/22/20 8:10 PM	A	healthy
10/22/20 9:20 PM	A	breakdown
10/22/20 10:10 PM	A	shutdown
10/22/20 11:05 PM	A	overheating
10/22/20 8:45 PM	A	healthy
10/22/20 8:50 PM	A	healthy
10/22/20 8:10 PM	B	healthy
10/22/20 9:20 PM	B	overheating
10/22/20 10:10 PM	B	healthy
10/22/20 11:05 PM	B	healthy
10/22/20 8:45 PM	B	healthy
10/22/20 8:50 PM	B	healthy

Now, I need to display the devices name if the sum of faults % (breakdown+overheating+Shutdown) is more than 25% in the last 24 hours and it should be diplay on a separate visual like this:

"Device (A) is having more than 25% faults"

the device name should come dynamically, also, if mutiple devices have more than 25% faults at time then it should display all the devices name like: "Device (A,B,C.....) having more than 25% faults"

Please suggest how to achieve this.

Thanks

[Icey · Accepted solution]

Hi @jitpbi ,

Try this:

Measure =
VAR t =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                'Table',
                "GT_Count_Percent_",
                    CALCULATE (
                        COUNT ( 'Table'[Faults] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[Device] = EARLIER ( 'Table'[Device] )
                                && 'Table'[Faults] IN { "breakdown", "overheating", "Shutdown" }
                        )
                    )
                        / CALCULATE (
                            COUNT ( 'Table'[Faults] ),
                            FILTER (
                                ALLSELECTED ( 'Table' ),
                                'Table'[Device] = EARLIER ( 'Table'[Device] )
                            )
                        )
            ),
            [GT_Count_Percent_] > 0.25
        ),
        [Device]
    )
VAR Device_ =
    CONCATENATEX ( t, [Device], ", " )
RETURN
    SWITCH (
        COUNTROWS ( t ),
        0, "No device is having more than 25% faults.",
        1,
            "Device (" & Device_ & ") is having more than 25% faults.",
        "Device (" & Device_ & ") are having more than 25% faults."
    )

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@jitpbi , Try a measure like

concatenatex(filter(summarize(Table, Table[Device], "_1", divide(calculate(countrows(Table), filter(Table, [Faults] in {"breakdown","overheating","Shutdown"})),countrows(Table))),[_1] >.25),[Device])