Need help with Grouping Data

[Anonymous]

Hello everybody! I can't GROUPBY my count of my purchases by count of my clients. 
That's user table:       
   

  

That's purchase's table:       
         

     
And that's what I want:

All this data must be filtered by date slice!

Help me please)

[Zubair_Muhammad]

Hi @Anonymous 

In your Purchases Table, you can add this calculated column

Please see attached file with your sample data

Count of Purchases =
CALCULATE (
    COUNT ( Purchases[UserId] ),
    ALLEXCEPT ( Purchases, Purchases[UserId] )
)

Then in Table Visual, you can drag the above calculated column and DistinctCount of UserId to get the desired result

[Anonymous]

@Zubair_Muhammad  Thank you for answer) But it work wrong when I filtring data with date slicer! Please check my example: https://www.dropbox.com/s/apwwg61aq7ty9or/ExampleWithDate.pbix?dl=0

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

try this approach.
See the attached file as well

First create a calculated table

NewTable =
SELECTCOLUMNS (
    GENERATESERIES ( 1, MAX ( Purchases[Count of Purchases] ) ),
    "Count of Purchases", [Value]
)

Then a Measure

No. of users =
COUNTROWS (
    FILTER (
        VALUES ( Purchases[UserId] ),
        CALCULATE ( COUNT ( Purchases[UserId] ) )
            = SELECTEDVALUE ( NewTable[Count of Purchases] )
    )
)

[Anonymous]

@Zubair_Muhammad , this is so great! But there is one thing: is there any way to filter list of users, when I click on the cell of "Count of Purchases"?

[Zubair_Muhammad]

@Anonymous 

I think you can establish a relationship between the columns "Count of Purchases" in Purchases and New Table

Then it would work

[Anonymous]

@Zubair_Muhammad Thank you! It works! I will try this on a large amount of data 😃