Get the repeated values ​​in a table, using, if possible, calculatedtable

[CLQ]

Hello everyone.

I have the following table (Table1):

Name	Order Date	Day	Month	Store
Jhon	2022/06/01	1	6	TV
Jhon	2022/06/01	1	6	TV
Jhon	2022/07/01	1	7	TV
Kelly	2022/06/01	1	6	Phone
Kelly	2022/06/01	5	6	Phone
Fred	2022/06/01	1	6	TV
Fred	2022/06/01	1	6	Phone
Mark	2022/06/05	5	6	TV
Mark	2022/06/05	5	6	Xbox
Jes	2022/08/12	12	8	TV

So, in a variable using calculatedtable, I look for the values ​​(Names) to be saved, which have bought two or more times in Store=TV or Xbox , in month six. The expected result, taking the table above, would be:

Jhon

Mark

What I tried, creating another table and trying to "simulate" the values ​​that said variable would bring, was:

CALCULATEDTABLE(VALUES(Table1[Name]),
NOT(Table1[Store]) = "Phone",
Table1[Month]=6,
FILTER(ALL(Table1), CALCULATE(COUNTROWS(Table1), ALL(Table1), VALUES(Table1[Name]))>=2)

But without result (it kept bringing me all the values ​​of month 6, regardless of the number of occurrences of "Name"). If anyone knows how to do it, I'd appreciate it 🙂

PS: for those who want to know the context of this:

My idea is, for example, in variable1, to obtain all the names that were repeated two or more times in month six.
Then, in variable2, get all the values ​​that appear in month 7, regardless of the number of times they appear.
The next step is, using INTERCEPT, to obtain the number of users that appear in variable2 that have appeared in variable1.
Obviously I have to create more rows to do this, but first I want for the first step to work out, which is the one that is not working for me 😑

[v-yinliw-msft · Accepted solution]

Hi @CLQ ,

You can try this method:

Result =
VAR _table =
    FILTER (
        'Table',
        AND ( 'Table'[Month] = 6, 'Table'[Store] = "TV" || 'Table'[Store] = "Xbox" )
    )
VAR _table1 =
    GROUPBY ( _table, [Name], "num", COUNTX ( CURRENTGROUP (), [Name] ) )
RETURN
    SUMMARIZE ( FILTER ( _table1, [num] > 1 ), [Name] )

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[Greg_Deckler]

@CLQ Try:

Table = 
  VAR __Table = GROUBY(FILTER('Table',[Store] <> "Phone" && [Month] = 6),[Name],"__Count",COUNTX(CURRENTGROUP(),[Order Date]))
  VAR __Result = FILTER(__Table,[__Count] > 1)
RETURN
  __Result