Filter top 3 rows for each userID in PowerBI

[Anonymous]

Hi all,   I am new to PowerBI. 

I am trying to get below desired output in 3-steps.

Screenshot: 

T1-Raw data : 

UserID	Group	Specialty
101	a1	S-51
101	a2	S-51
101	a3	S-51
101	a2	S-51
101	a5	S-51
101	a1	S-51
101	a2	S-51
201	a1	S-9
201	a3	S-9
201	a3	S-9
201	a4	S-9
201	a5	S-9
201	a5	S-9
201	a1	S-9
301	a1	S-15
301	a1	S-15
301	a5	S-15

Step -1 (T2-count per group per user) : 

Here I can use a measure , but I am making a summary table for understanding purpose.

summary = SUMMARIZE(T1-Raw data,SampleData[UserID],T1-Raw data[Specialty],T1-Raw data[Group],"countof",count(T1-Raw data[Group]))

Step -2 (T3- Sorted and top-3 rows filtered for each UserID) : 

I am not getting any logic for this ..I tried this simple ranking ,but its not working.

Rank = RANKX('T1-Raw data','T1-Raw data'[countof],,ASC,Dense)

 Step -3 (T4-Final output using CONCATENATEX) : 

Here I can use CONCATENATEX function to show the the data in single row for each user.

So can someone help me for step-2?

[Anonymous · Accepted solution]

Hi @Anonymous ，

Create 2 measures as below:

Measure= CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[UserID]=MAX('Table'[UserID])))

T2 = IF(RANKX(FILTER(ALL('Table'),'Table'[UserID]=MAX('Table'[UserID])&&'Table'[Specialty]=MAX('Table'[Specialty])), 
RANKX(FILTER(ALL('Table'),'Table'[UserID]=MAX('Table'[UserID])&&'Table'[Specialty]=MAX('Table'[Specialty])),CALCULATE(COUNTA('Table'[Group])),,DESC,Dense)+
RANKX(FILTER(ALL('Table'),'Table'[UserID]=MAX('Table'[UserID])&&'Table'[Specialty]=MAX('Table'[Specialty])),CALCULATE(MAX('Table'[Group])),,ASC,Dense)
/COUNTROWS(FILTER(ALL('Table'),'Table'[UserID]=MAX('Table'[UserID]))),,ASC,Dense)<=3,'Table'[Measure 2],BLANK())

And you will see:

And for D1 and D2,see below:

_D1 = CONCATENATEX(FILTER(DISTINCT('Table'),'Table'[T2]<>BLANK()),'Table'[Group],",")

_D2 = CONCATENATEX(FILTER(DISTINCT('Table'),'Table'[T2]<>BLANK()),'Table'[Measure],",")

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks for providing me this solution, But I am not able to understand how it can work if there is no specailty  column. I think you are groupby back with specailty  which I am not willing. I want to rank and concat the top3 values only w.r.t each user.

Can you provide some explaination for the same?

[harshnathani]

HI @Anonymous ,

You can try these measures.

D1 =
CONCATENATEX (
    TOPN (
        3,
        SUMMARIZE (
            'Table',
            'Table'[UserID],
            'Table'[Specialty],
            'Table'[Group],
            "countof", CALCULATE (
                COUNT ( 'Table'[Group] ),
                FILTER (
                    'Table',
                    'Table'[UserID]
                        = MAX ( 'Table'[UserID] )
                        && 'Table'[Specialty]
                            = MAX ( 'Table'[Specialty] )
                )
            )
        ),
        [countof]
    ),
    'Table'[Group],
    ","
)

D2 =
CONCATENATEX (
    TOPN (
        3,
        SUMMARIZE (
            'Table',
            'Table'[UserID],
            'Table'[Specialty],
            'Table'[Group],
            "countof", CALCULATE (
                COUNT ( 'Table'[Group] ),
                FILTER (
                    'Table',
                    'Table'[UserID]
                        = MAX ( 'Table'[UserID] )
                        && 'Table'[Specialty]
                            = MAX ( 'Table'[Specialty] )
                )
            )
        ),
        [countof]
    ),
    [countof],
    ","
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thanks for providing me this solution, But I am not able to understand how it can work if there is no specailty  column. I think you are groupby back with specailty  which I am not willing. I want to rank and concat the top3 values only w.r.t each user.

Can you provide some explaination for the same?