Latest Enterprise DNA Initiatives

Count Number of 0% Occurrences in a measure

HI,

Id like to count the number of 0% occurrences that appear from my Utilization Measure column in this table. in the example i provide the answer should be 2 not 152 which i believe is looking at all entries in the head count table.

Im close i feel but missing something from the virtual table.

See screen shot and PBIX

Number Of 0 % Utilizations =
COUNTROWS (
FILTER (
SUMMARIZE (
‘Head Count’,
‘Head Count’[Employee Number],
“Is 0”, [Total Employee Utilization %]
),
[Is 0] = 0
)
)

EDNA Count If 0%.pbix (1.2 MB)

Hi,

Is anyone able to help me?

Hi @Krays23,

Please note that this is not an on-demand forum, so it might take some time for members who give up their free time to provide support, to have a chance to review and respond to your question.

I’ve just taken a look and it seems you’re missing some external context, so give this a go.

Number Of 0 % Utilizations v2 = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS(
            SUMMARIZE(
                'SAP SQ00 Confirmations',
                Dates[Date],
                'Work Centers & Areas'[Area],
                'Head Count'[Employee Number]
            ),  "Is 0", ROUND([Total Employee Utilization %], 2)
        ),  [Is 0] = 0
    )
)

I hope this is helpful.

5 Likes

Thanks Melissa,

Understood my apologies.

Actually i figured it out in the end i did this which works as well.

Number Of > 100 % Utilizations = 
VAR VTable =
    SUMMARIZE (
        'SAP SQ00 Confirmations',
            'SAP SQ00 Confirmations'[Execution Start Date],
            'SAP SQ00 Confirmations'[Employee Number],
            "Is 0", [Total Employee Utilization %]
        )
RETURN
    COUNTX ( FILTER ( VTable, [Is 0] > 1 ), [Is 0] )

Cheers

Dan