SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

[Anonymous]

Hello,

I am trying to create a measure that allows me to see ONLY the values that repeat more than 2 times. 

Using this as an example, I would like to show only the $23000 and the $24000 because they repeat more than 2 times. 

$24,500
$23,000
$23,000
$23,000
$27,800
$27,800
$24,000
$24,000
$24,000

I have been trying to use the count, contain and groupby formula but I can't figure it out. 

Your help will be greatly appreciated. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous

create a measure

Measure = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[Column1]))

add this measure in the visual level filter

Best Regards

Maggie

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

[Greg_Deckler]

You could create a table like this:

Table = 
VAR __table = SUMMARIZE('Table',[Value],"__count",COUNT([Value]))
VAR __table1 = FILTER(__table,[__count] >= 3)
RETURN
__table1

or a measure you can filter on:

Table = 
VAR __value = MAX([Value])
VAR __table = SUMMARIZE('Table',[Value],"__count",COUNT([Value]))
VAR __table1 = FILTER(__table,[Value] = __value)
RETURN
MAXX(__table1,[__count])

[Anonymous]

I am doing it but I get this error. " The expression specified in the query is not a valid table expression." 

Multiples of Tens = 
Var _Value=MAX(ECS_EZQuery40[DEBIT_AMOUNT])
Var _Multiple10=SUMMARIZE(ECS_EZQuery40,ECS_EZQuery40[DEBIT_AMOUNT],"COUNT",COUNT(ECS_EZQuery40[DEBIT_AMOUNT]))
VAR _Mulriple102=FILTER(ECS_EZQuery40,ECS_EZQuery40[DEBIT_AMOUNT]=ECS_EZQuery40[DEBIT_AMOUNT])
RETURN
MAXX(ECS_EZQuery40,ECS_EZQuery40[DEBIT_AMOUNT]
)

Multiples of Tens = 
Var _Value=MAX(ECS_EZQuery40[DEBIT_AMOUNT])
Var _Multiple10=SUMMARIZE(ECS_EZQuery40,ECS_EZQuery40[DEBIT_AMOUNT],"COUNT",COUNT(ECS_EZQuery40[DEBIT_AMOUNT]))
VAR _Mulriple102=FILTER(ECS_EZQuery40,ECS_EZQuery40[DEBIT_AMOUNT]=ECS_EZQuery40[DEBIT_AMOUNT])
RETURN
MAXX(ECS_EZQuery40,COUNT(ECS_EZQuery40[DEBIT_AMOUNT])
)