Forum Discussion

RicLup's avatar
RicLup
Icon for Helper III rankHelper III
6 years ago
Solved

create new column based in different values to assign status

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!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

     

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      RicLup
      Icon for Helper III rankHelper III
      6 years ago

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

       

      Regards!!