Conditional Calculated Column with Groupings

[Anonymous]

Hello everyone, I am still new to DAX and I have a situation here that I've been looking to solve for a few days.

I have a table below and I am trying to populate Account_Status. The logic is bascially if the Unique_ID is same (group) and if any field in Product_Subscription is = 'Active' for this group, then Account_Status should be Active. How do I get this kind of output?

Thank you in advance!

Unique_ID	Product	Product_Subscription	Account_Status
100	P1	Active	Active
100	P1	Inactive	Active
100	P2	Inactive	Active
100	P3	Active	Active
200	P1	Inactive	Active
200	P2	Active	Active
200	P3	Inactive	Active
200	P3	Active	Active
200	P3	Active	Active
300	P2	Inactive	Inactive
300	P2	Inactive	Inactive

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try like this:

Account_Status =
VAR CountActive =
    CALCULATE (
        COUNTROWS( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Unique_ID] ),
            'Table'[Product_Subscription] = "Active"
        )
    )
RETURN
    IF ( CountActive > 0, "Active", "Inactive" )

[Anonymous]

Thank you very much! This worked 🙂