Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Filter the values based on conditions on other two columns

Hi All,

I need help in below scenario

I need to consider the values based on few conditions 
1) if count(ProblemId)>1 && Priority = "Critical"(if we have 1 value with critical status, 2 values with "High" status i need to consider "Critical" one)

2) if count(Problemid)>1(if we have 2 values with "High" status i need to consider one value randomly)

3)  if Count(Problemid)=1 (regardless of Priority status we need to consider this)

 

 

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous ,

     

    Please try the following formula as a calculated column. You can customize it for you own needs. Please check out the demo in the attachment.

    Column =
VAR criticalCount =
    CALCULATE (
        COUNT ( Table1[ProblemId] ),
        Table1[Priority] = "Critical",
        ALL ( Table1[Index], Table1[Status] )
    )
VAR highCount =
    CALCULATE (
        COUNT ( Table1[ProblemId] ),
        Table1[Priority] = "High",
        ALL ( Table1[Index], Table1[Status] )
    )
VAR allCount =
    CALCULATE (
        COUNT ( Table1[ProblemId] ),
        ALLEXCEPT ( Table1, Table1[ProblemId] )
    )
RETURN
    IF (
        [Priority] = "Critical"
            && criticalCount = 1
            && allCount = 3,
        "consider",
        IF (
            allCount = 3
                && highCount = 2,
            "Remove",
            IF (
                highCount = 2
                    && allCount = 2
                    && [Index] = CALCULATE ( MAX ( [Index] ), ALLEXCEPT ( Table1, Table1[ProblemId] ) ),
                "considerHigh2",
                IF ( highCount = 1 && allCount = 1, "considerOnly1", "else" )
            )
        )
    )

    filter

     

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Did it work? Can you mark it as an answer?

     

    Best Regards,

    Dale