Forum Discussion

CLQ's avatar
CLQ
Icon for Helper I rankHelper I
3 years ago
Solved

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

Hello everyone.

I have the following table (Table1):

NameOrder Date DayMonthStore
Jhon2022/06/0116TV
Jhon2022/06/0116TV
Jhon2022/07/0117TV
Kelly2022/06/0116Phone
Kelly2022/06/0156Phone
Fred2022/06/0116TV
Fred2022/06/0116Phone
Mark2022/06/0556TV
Mark2022/06/0556Xbox
Jes2022/08/12128TV

 

 

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity 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.