locked
Returning values based on other columns RRS feed

  • Question

  • I've got these columns and I'd like the third column to return either closed or active depending of if and ID has been closed. For the like ID's, it will check the state column if there is one where it is closed, if there is it'll return closed and if there isn't it'll return Active.

    Thanks for the help.

    Tuesday, February 6, 2018 4:50 PM

Answers

  • Hi jshinnenkamp,

    Thanks for your question.

    As i said in your previous thread Removing Duplicates questions, I would suggest you to do this in the underlying database. If you do want to do this as a calculated column,please try below DAX formula:

    =
    IF (
        COUNTROWS (
            FILTER ( 'Work Item', 'Work Item'[ID] = EARLIER ( 'Work Item'[ID] ) )
        )
            = 1,
        'Work Item'[state],
        IF (
            COUNTROWS (
                FILTER (
                    'Work Item',
                    'Work Item'[ID] = EARLIER ( 'Work Item'[ID] )
                        && 'Work Item'[state] = "close"
                )
            ),
            "close",
            "Active"
        )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 7, 2018 1:22 AM

All replies

  • Hi jshinnenkamp,

    Thanks for your question.

    As i said in your previous thread Removing Duplicates questions, I would suggest you to do this in the underlying database. If you do want to do this as a calculated column,please try below DAX formula:

    =
    IF (
        COUNTROWS (
            FILTER ( 'Work Item', 'Work Item'[ID] = EARLIER ( 'Work Item'[ID] ) )
        )
            = 1,
        'Work Item'[state],
        IF (
            COUNTROWS (
                FILTER (
                    'Work Item',
                    'Work Item'[ID] = EARLIER ( 'Work Item'[ID] )
                        && 'Work Item'[state] = "close"
                )
            ),
            "close",
            "Active"
        )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 7, 2018 1:22 AM
  • Hi jshinnenkamp,

    Thanks for your question.

    I’m writing to follow up with you on this post. Was the problem resolved after performing my action plan steps?

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated. If you still have any problems with this question, Please feel free to let me know.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 14, 2018 1:17 AM