DAX: If () with Group_by on (id) and conditions on other columns

[uk-roberto92]

Hi, I have a table of the form:

id  case  status
0    1       xx
0    555     yy
0    125     zz
2     87     yy
2     nn     xx

And I am trying to code something like the below:

for every id if 1 case has status xx and 1 case has status yy, THEN I want yy = xx

So the resulting table would be

id   case    status
0     1        xx
0     555      **xx**
0     125      zz
2     87       **xx**
2     nn       xx

I tried with 

Column=
IF (
    MAXX (
        FILTER (
            'Table',
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[status] = "xx"
        ),
        'Table'[status]
    ) = "",
    "",
    "xx"
)

but that doesn't work as it would transform all the *statuses* per *id* into -xx-.

Any help on this?

Thanks

[johnt75 · Accepted solution]

You can create a calculated column like

Actual Status = IF( 'Table'[status] = "yy",
    VAR NumXX = CALCULATE(
        COUNTROWS( 'Table' ),
        ALLEXCEPT( 'Table', 'Table'[id]),
        'Table'[status] = "xx"
    )
    VAR NumYY = CALCULATE(
        COUNTROWS( 'Table' ),
        ALLEXCEPT( 'Table', 'Table'[id] ),
        'Table'[status] = "yy"
    )
    VAR Result = IF( NumXX = 1 && NumYY = 1, "xx", 'Table'[status] )
    RETURN Result,
    'Table'[status]
)