Derive a new column based on other values

Hello POSPOS 

Try this DAX code to create column

New Column =

VAR CurrentUser = 'Table'[User Name]

VAR HasWithFund =

    CALCULATE(

        COUNTROWS('Table'),

        'Table'[User Name] = CurrentUser,

        'Table'[Status] = "With Fund"

    ) > 0

VAR HasNoFund =

    CALCULATE(

        COUNTROWS('Table'),

        'Table'[User Name] = CurrentUser,

        'Table'[Status] = "No Fund"

    ) > 0

RETURN

    IF(HasWithFund, "With Fund",

        IF(HasNoFund, "No Fund", "Exclude")

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Hi POSPOS ,

You want to derive a new column using DAX based on the overall presence of statuses per user, not just per row. So even if one row for a user is "No Fund" or "Exclude", if any row for that user is "With Fund", then all rows for that user should get "With Fund".

This screams calculated column using a DAX logic like “look at all rows for the same user and check if any match ‘With Fund’, then fallback to ‘No Fund’, otherwise ‘Exclude’.”

Here’s the DAX magic that gets the job done:

New Column =
VAR HasWithFund =
    CALCULATE(
        COUNTROWS('YourTable'),
        ALLEXCEPT('YourTable', 'YourTable'[User Name]),
        'YourTable'[Status] = "With Fund"
    )
VAR HasNoFund =
    CALCULATE(
        COUNTROWS('YourTable'),
        ALLEXCEPT('YourTable', 'YourTable'[User Name]),
        'YourTable'[Status] = "No Fund"
    )
RETURN
    IF(
        HasWithFund > 0,
        "With Fund",
        IF(
            HasNoFund > 0,
            "No Fund",
            "Exclude"
        )
    )

This works because:

• ALLEXCEPT keeps the filter context only for the 'User Name' so you're looking at the status across all rows for that user.
• We check "With Fund" first because it's the top priority.
• Then "No Fund" gets the second place trophy.
• If none of the above, they’re stuck in "Exclude" land.

Let me know if your actual table has a different name or you want to make this dynamic for visuals — that’d be a measure instead of a column.

Best regards,