Create a Measure to display most frequent category with conditions.

[joyc0025]

How can I create a DAX measure to display the most frequent non-compliance outcome for a given month on a card? The measure should exclude rows with "Y" in the compliance column and "NA" or 'Nil' in the non-compliance category column. In case of ties, the measure should use severity as the tiebreaker (Low, Medium, High - assume 1,2 3 if helps?).

Initially assumed it would be straightforward, I have spent hours experimenting with various approaches involving filters, calculated tables, addcolumns, summarize, and rankx functions. Now i'm just more confused and going in circles.

Table: Compliance

 The desired outcome for March 2023, the card would display "Fraud".

Any assistance or guidance would be greatly appreciated. Thank you in advance.

[joyc0025]

This solution is perfect, thank you so much 🙂

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.

Expected outcome measure: =
VAR _t =
    ADDCOLUMNS (
        FILTER (
            Compliance,
            Compliance[Compliance] <> "Y"
                && NOT ( Compliance[Non Compliance] IN { "NA", "Nil" } )
        ),
        "@tiebreaker",
            SWITCH (
                TRUE (),
                Compliance[Severity] = "High", 3,
                Compliance[Severity] = "Medium", 2,
                Compliance[Severity] = "Low", 1
            )
    )
VAR _grouptable =
    GROUPBY (
        _t,
        Compliance[Date],
        Compliance[Non Compliance],
        "@count", SUMX ( CURRENTGROUP (), 1 ),
        "@tiebreak", SUMX ( CURRENTGROUP (), [@tiebreaker] )
    )
VAR _highest =
    MAXX (
        ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
        [@value]
    )
RETURN
    IF (
        HASONEVALUE ( Compliance[Date] ),
        CONCATENATEX (
            FILTER (
                ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
                [@value] = _highest
            ),
            Compliance[Non Compliance],
            ", "
        )
    )