DAX to identify when a value is 0 in a group of values

[vini_udenia]

The following DAX does not return correct results when the number of Variants is 2 and Atleast 1 of then is 0. The grouping is not working as requried and will appreciate any help here.

Result =

IF (

    [Variant Count] > 2,

    "Variant count is greater than 2",

    IF (

        CALCULATE(

        DISTINCTCOUNT ( 'IBIS SUBJECT_VARIANT_APPROVAL'[Variant]  ),

        ALLEXCEPT (

           'IBIS SUBJECT_VARIANT_APPROVAL',

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Session],

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Subject],

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Subject Option Default],

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Level],

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Language],

                        'IBIS SUBJECT_VARIANT_APPROVAL'[Exam Zone]

        )

    ) = 2,

        IF (

            COUNTROWS ( FILTER ( 'IBIS SUBJECT_VARIANT_APPROVAL', 'IBIS SUBJECT_VARIANT_APPROVAL'[Variant] = "0" ) ) >= 1,

        "At least 1 is 0",

        "Both are non 0"

    ),

        "Variant count is less than 2"

    )

OUTPUT:

Subject	Subject Option Default	Level	Language	Exam Zone	COMPONENT	Variant	Variant Count	Result
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	A	PAPER ONE	0	2	At least one variant is 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	A	PAPER TWO LISTENING	1	2	Both variants are not 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	A	PAPER TWO READING	1	2	Both variants are not 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	B	PAPER ONE	0	2	At least one variant is 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	B	PAPER TWO LISTENING	1	2	Both variants are not 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	B	PAPER TWO READING	1	2	Both variants are not 0
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	C	PAPER ONE	0	3	Variant count is greater than 2
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	C	PAPER TWO LISTENING	1	3	Variant count is greater than 2
CHINESE B	CHINESE B - CANTONESE	HL	CHINESE	C	PAPER TWO READING	3	3	Variant count is greater than 2
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	A	PAPER ONE	0	2	At least one variant is 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	A	PAPER TWO LISTENING	1	2	Both variants are not 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	A	PAPER TWO READING	1	2	Both variants are not 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	B	PAPER ONE	0	2	At least one variant is 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	B	PAPER TWO LISTENING	1	2	Both variants are not 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	B	PAPER TWO READING	1	2	Both variants are not 0
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	C	PAPER ONE	0	3	Variant count is greater than 2
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	C	PAPER TWO LISTENING	1	3	Variant count is greater than 2
CHINESE B	CHINESE B - MANDARIN	HL	CHINESE	C	PAPER TWO READING	3	3	Variant count is greater than 2

[vini_udenia]

@Venkateshchiluk Thank you for the suggestion but it is still returning the same result

[Venkateshchiluk]

Hi @vini_udenia Please try belowgiven dax
Result =
IF (
[Variant Count] > 2,
"Variant count is greater than 2",
IF (
CALCULATE (
DISTINCTCOUNT ( 'IBIS SUBJECT_VARIANT_APPROVAL'[Variant] ),
ALLEXCEPT (
'IBIS SUBJECT_VARIANT_APPROVAL',
'IBIS SUBJECT_VARIANT_APPROVAL'[Session],
'IBIS SUBJECT_VARIANT_APPROVAL'[Subject],
'IBIS SUBJECT_VARIANT_APPROVAL'[Subject Option Default],
'IBIS SUBJECT_VARIANT_APPROVAL'[Level],
'IBIS SUBJECT_VARIANT_APPROVAL'[Language],
'IBIS SUBJECT_VARIANT_APPROVAL'[Exam Zone]
)
) = 2,
IF (
COUNTROWS (
FILTER (
'IBIS SUBJECT_VARIANT_APPROVAL',
'IBIS SUBJECT_VARIANT_APPROVAL'[Variant] = "0"
)
) >= 1,
"At least 1 is 0",
"Both are non 0"
),
"Variant count is less than or equal to 2"
)
)