Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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?
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)
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |