Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EgorS
Frequent Visitor

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:       
   

  Users.jpg

That's purchase's table:       
         

      purchases.jpg
And that's what I want:

What_I_Want.jpg
All this data must be filtered by date slice!

Help me please)

1 ACCEPTED SOLUTION

Hi @EgorS 

 

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] )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

Hi @EgorS 

 

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


Regards
Zubair

Please try my custom visuals

@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

Hi @EgorS 

 

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] )
    )
)

Regards
Zubair

Please try my custom visuals

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

@EgorS 

 

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

 

Then it would work


Regards
Zubair

Please try my custom visuals

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.