Count of ID if column A is greater than 25 or column B is greater than 250

[The_Clam]

Hi.  I need some help please.  

I would like a count of ID if column A is greater than 25 or column B is greater than 250. The two tricky parts I cannot quite grasp are: 1) If both conditions are TRUE, then it should only count as 1, not 2.  And 2) There are duplicate IDs in the dataset, so the SUM of these IDs should be above the thresh-hold, not individually.

I have attached a link to the Power BI file.  For the dataseset in the file, the number I am looking for is: 24

Thanks!

Power BI file: 

https://drive.google.com/file/d/11GRAerF7N79UY-OrOX_7DqzrpktoLLc6/view?usp=sharing

Small sample of dataset:

ID	A	B
28483	30	0
28629	0	1469905
28631	0	1469907
28987	40	0
28989	50	0
29023	8	0
29035	8	0
29203	1	0
29205	1	0
29213	1	0
29215	1	0
29343	8	0
29429	1	0
29433	8	0
29441	14	0
29441	13	0

[v-yangliu-msft · Accepted solution]

Hi  @The_Clam ,

Here are the steps you can follow：

1. Create calculated table.

Table =
FILTER('Worksheet','Worksheet'[A]>25 || 'Worksheet'[B]>250)

Table Create calculated column:

count =
COUNTX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])),[ID])

2. Create measure.

Flag =
var _Count1=COUNTX(FILTER(ALL('Table'),'Table'[count]=1),[ID])
var _Count2=COUNTX(FILTER(ALL('Table'),[count]=3),[ID])
var _Count3 =
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[count]=2))
return
_Count1 + _Count2 - _Count3

3. Result:

If you need pbix, please click here.

Not very clear the meaning of thresh-hold, here I use the number of each group to distinguish.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly