cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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)

2 REPLIES 2
Microsoft Employee

Hi @Anonymous,

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

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

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" )
)
)
)
```

Best Regards,

Dale

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