Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help with Grouping Data

  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 ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Zubair_Muhammad , this is so great!:smileyhappy: 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's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

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

     

    Then it would work

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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