topic Re: Get the number of repeated text values that are higher than 3 by ID in DAX Commands and Tips

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

NumeroENAP — Wed, 04 Mar 2020 17:03:55 GMT

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

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

az38 — Wed, 04 Mar 2020 18:20:15 GMT

Hi NumeroENAP

try create a calculated table

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

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

NumeroENAP — Wed, 04 Mar 2020 18:56:28 GMT

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.

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

az38 — Wed, 04 Mar 2020 19:01:38 GMT

NumeroENAP

are you creating new table? not column or measure?

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

NumeroENAP — Wed, 04 Mar 2020 19:11:54 GMT

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

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

NumeroENAP — Wed, 04 Mar 2020 19:15:41 GMT

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.

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

az38 — Wed, 04 Mar 2020 19:16:39 GMT

NumeroENAP

ok, create a measure and filter by its value

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

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

NumeroENAP — Wed, 04 Mar 2020 19:21:22 GMT

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

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

NumeroENAP — Wed, 04 Mar 2020 22:31:05 GMT

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