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 wit...
Need Help
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