DAX for grouping value

[Anonymous]

Hi, 

I have been trying to create a dax code that would regroup my data like below:

ID	Status	FREQ		ID	Status
101173150	Compliant	1		101173150	Compliant
101173547	Compliant	2		101173547	Compliant
101173547	First	2		101173547	Compliant
101173549	Compliant	2		101173549	Not Compliant
101173549	Not Compliant	2		101173549	Not Compliant
101173571	Not Compliant	1		101173571	Not Compliant
101173572	Not Compliant	2		101173572	Not Compliant
101173572	Last	2		101173572	Not Compliant
101173702	Compliant	1		101173702	Compliant
101173706	Compliant	2		101173706	Not Compliant
101173706	Not Compliant	2		101173706	Not Compliant
101173815	Compliant	2		101173815	Not Compliant
101173815	Not Compliant	2		101173815	Not Compliant
101173847	Not Compliant	1		101173847	Not Compliant
101173868	Compliant	3		101173868	Not Compliant
101173868	Not Compliant	3		101173868	Not Compliant
101173868	First	3		101173868	Not Compliant

The scenario is that when there are multiple values for one ID I would prioritise "not compliant" status if "not compliant" is not there i would take "compliant" over "first" or "last" status.

[Icey · Accepted solution]

Hi @Anonymous ,

There is a small error in the expression provided by @amitchandak. Please modify it as follows:

New column = 
VAR _nc =
    COUNTX (
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" ),
        [ID]
    )
VAR _c =
    COUNTX (
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" ),
        [ID]
    )
VAR _f =
    COUNTX (
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ),
        [ID]
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( _nc ) ), "Not Compliant",
        NOT ( ISBLANK ( _c ) ), "Compliant",
        NOT ( ISBLANK ( _f ) ), "First",
        "Last"
    )

Or try this:

Column = 
VAR _nc =
    COUNTROWS (
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" )
    )
VAR _c =
    COUNTROWS (
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" )
    )
VAR _f =
    COUNTROWS ( 
        FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ) )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( _nc ) ), "Not Compliant",
        NOT ( ISBLANK ( _c ) ), "Compliant",
        NOT ( ISBLANK ( _f ) ), "First",
        "Last"
    )

Measure =
VAR _nc =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ID] = MAX ( [ID] )
                && [Status] = "Not Compliant"
        )
    )
VAR _c =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ID] = MAX ( [ID] )
                && [Status] = "Compliant"
        )
    )
VAR _f =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Table' ), [ID] = MAX ( [ID] ) && [Status] = "First" )
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( _nc ) ), "Not Compliant",
        NOT ( ISBLANK ( _c ) ), "Compliant",
        NOT ( ISBLANK ( _f ) ), "First",
        "Last"
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , a New column like

New column =
var _nc = countx(filter(table, [ID] =earlier([ID]), [Status] ="Not Compliant"),[ID])
var _c = countx(filter(table, [ID] =earlier([ID]), [Status] ="Compliant"),[ID])
var _f = countx(filter(table, [ID] =earlier([ID]), [Status] ="First"),[ID])
return
Switch( true(),
not(isblank(_nc)) , "Not Compliant",
not(isblank(_c)) , "Compliant",
not(isblank(_f)) , "First",
"Last"
)