create new column based in different values to assign status

[RicLup]

Hi everyone:

I Hope you can help me. I need create a new column to assign one status bassed in differents conditions and values:

I have two scenarios for the same table by ID:

Scenario 1:

 I need the status Assigned in all ID rows, only if at least my field ReleasedConfirm contain one value in a row with FALSE.

Scenario 2:

If the field ReleasedConfirm for the same ID contain value True, place the Assigned Status in that row,  and when have not projectId number place Free Status in thats rows.

These are my scenarios for the same table by differents ID, to create  one column status with the contions explained above.

Regards and thanks for your time to help me!

[v-juanli-msft · Accepted solution]

Hi @RicLup 

If [ReleasedConfirm] column of "True/False" type, please create a column below:

Column =
VAR counts =
    CALCULATE (
        COUNT ( Sheet1[ID] ),
        FILTER (
            ALLEXCEPT (
                Sheet1,
                Sheet1[ID]
            ),
            [ReleasedConfirm]
                = FALSE ()
                && NOT (
                    ISBLANK ( [ReleasedConfirm] )
                )
        )
    )
RETURN
    IF (
        counts > 0,
        "Assigned",
        IF (
            [ProjectId]
                <> BLANK (),
            "Assigned",
            "Free"
        )
    )

If [ReleasedConfirm] column of "text" type, please create a column below:

Column 3 =
VAR counts =
    CALCULATE (
        COUNT ( Sheet1[ID] ),
        FILTER (
            ALLEXCEPT (
                Sheet1,
                Sheet1[ID]
            ),
            FIND (
                "FALSE",
                [ReleasedConfirm2],
                1,
                0
            ) > 0
        )
    )
RETURN
    IF (
        counts > 0,
        "Assigned",
        IF (
            [ProjectId]
                <> BLANK (),
            "Assigned",
            "Free"
        )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RicLup]

Thank you very much for the quick answer, it works great.

Regards!!