Forum Discussion
Get the repeated values in a table, using, if possible, calculatedtable
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 😑
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.
2 Replies
- Greg_Deckler
Community Champion
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 - v-yinliw-msft
Community Support
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.