cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pcastro Regular Visitor
Regular Visitor

SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

Hi @pcastro

create a measure

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

add this measure in the visual level filter

16.png

 

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.

3 REPLIES 3
Super User
Super User

Re: SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


pcastro Regular Visitor
Regular Visitor

Re: SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

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])
)
Community Support Team
Community Support Team

Re: SHOW ONLY MATCHING VALUES THAT REPEAT MORE THAN 2 TIMES

Hi @pcastro

create a measure

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

add this measure in the visual level filter

16.png

 

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.