Derive a new column based on other values

[POSPOS]

Hi All,

I have a requirement as below: 

Sample data:

User Name	Status	New Column
A	No Fund	With Fund
A	With Fund	With Fund
A	Exclude	With Fund
B	No fund	No fund
B	No fund	No fund
C	No Fund	No Fund
C	Exclude	No Fund
C	Exclude	No Fund
C	No Fund	No Fund
D	With Fund	With Fund
D	With Fund	With Fund
D	Exclude	With Fund
E	Exclude	Exclude
E	Exclude	Exclude

I have Username and Status column. Requirement is to create a new column using DAX  based on the below criteria:

If, user name has "With Fund" as one of the status, then the new column="With Fund"

If, user name does not have  "With Fund" and has "No Fund" as one of the status, then the new column="No Fund"

If, user name has only "Exclude" , then the new column="Exclude"

I would like to do this using DAX and not M query as there are other logics behind the data. The data provide above is a sample data only.

Thank you.

[pankajnamekar25 · Accepted solution]

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.

[DataNinja777 · Accepted solution]

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,

[pankajnamekar25 · Accepted solution]

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.