Get the number of repeated text values that are higher than 3 by ID

[NumeroENAP]

Hi, 

Table exemple : 

GROUP_NO	ID	VALUE
100	1	Banana
101	1	Banana
102	1	Banana
103	1	Apple
104	1	Apple

So, I want to get : 

ID                             1

   / Name                  Banana

         / Value             3

So, in the end, if my table containt 3 values "banana" with ID #1, I want it to appear in my report. But, with the same ID, if the value «apple» appear only twice, I don't want it to appear in my report. 

Thanks

[az38]

Hi @NumeroENAP 

try create a calculated table

Table 2 = FILTER(SUMMARIZE('Table','Table'[ID],'Table'[VALUE],"Count",COUNT('Table'[VALUE])),[Count]>2)

[NumeroENAP]

Hmmm, it doesn't work. It tells me that my query is refering to multiple columns (ID and Value) and that those can't be converted to scalar values.

[az38]

@NumeroENAP 

are you creating new table? not column or measure?

[NumeroENAP]

The best I did so far was to create a calculated column to count all different values :

= COUNTX(FILTER(GROUPS, EARLIER(GROUPS[VALUES])=GROUPES_FRA[VALUES]),GROUPS[VALUES])

But, in my report, it always give it for all IDs. So, I need to add something to show it per ID, and that can also show it by value name for those which are 3 or higher. 

[NumeroENAP]

Well, for the needs of the user, I have to do this in Excel, so... I'm not used with calculated table in Excel (I don't even know if it's possible).

[az38]

@NumeroENAP 

ok, create a measure and filter by its value

Measure = calculate(countrows('Table'),allexcept('Table','Table'[ID],'Table'[VALUE]))

[NumeroENAP]

This is the result : 

ID  1

     Value 1      108

     Value 2          2

     Value 3      101

ID  2

     Value 1      108

     Value 2          2

     Value 3      101

[NumeroENAP · Accepted solution]

Solution :
COUNT_VALUES_BY_ID:=CALCULATE(COUNTX('Table','Table'[Value]),FILTER('Table',COUNTX('Table','table'[Value])>2))

After that, I needed to play into the options of my dynamic table (uncheck totals and allow multiple filters by field).