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
vini_udenia
Helper I
Helper I

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

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:
SubjectSubject Option DefaultLevelLanguageExam ZoneCOMPONENTVariantVariant CountResult
CHINESE BCHINESE B - CANTONESEHLCHINESEAPAPER ONE02At least one variant is 0
CHINESE BCHINESE B - CANTONESEHLCHINESEAPAPER TWO LISTENING12Both variants are not 0
CHINESE BCHINESE B - CANTONESEHLCHINESEAPAPER TWO READING12Both variants are not 0
CHINESE BCHINESE B - CANTONESEHLCHINESEBPAPER ONE02At least one variant is 0
CHINESE BCHINESE B - CANTONESEHLCHINESEBPAPER TWO LISTENING12Both variants are not 0
CHINESE BCHINESE B - CANTONESEHLCHINESEBPAPER TWO READING12Both variants are not 0
CHINESE BCHINESE B - CANTONESEHLCHINESECPAPER ONE03Variant count is greater than 2
CHINESE BCHINESE B - CANTONESEHLCHINESECPAPER TWO LISTENING13Variant count is greater than 2
CHINESE BCHINESE B - CANTONESEHLCHINESECPAPER TWO READING33Variant count is greater than 2
CHINESE BCHINESE B - MANDARINHLCHINESEAPAPER ONE02At least one variant is 0
CHINESE BCHINESE B - MANDARINHLCHINESEAPAPER TWO LISTENING12Both variants are not 0
CHINESE BCHINESE B - MANDARINHLCHINESEAPAPER TWO READING12Both variants are not 0
CHINESE BCHINESE B - MANDARINHLCHINESEBPAPER ONE02At least one variant is 0
CHINESE BCHINESE B - MANDARINHLCHINESEBPAPER TWO LISTENING12Both variants are not 0
CHINESE BCHINESE B - MANDARINHLCHINESEBPAPER TWO READING12Both variants are not 0
CHINESE BCHINESE B - MANDARINHLCHINESECPAPER ONE03Variant count is greater than 2
CHINESE BCHINESE B - MANDARINHLCHINESECPAPER TWO LISTENING13Variant count is greater than 2
CHINESE BCHINESE B - MANDARINHLCHINESECPAPER TWO READING33Variant count is greater than 2
2 REPLIES 2
vini_udenia
Helper I
Helper I

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

Venkateshchiluk
Frequent Visitor

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"
)
)

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.