topic Re: Best Rank within category in DAX Commands and Tips

Best Rank within category

pbininja — Wed, 06 May 2020 04:36:19 GMT

Dear all,

So what I am looking at is finding out the highest/best Rank for the 'Social network' and the month which had this best Rank. Just attaching a sample data. The original data runs into a million rows for multiple months. Would want a measure ideally to calculate the measure, so that if I have a slicer to Social network and Gender, it would give me the best rank and for which month and the followers in that best ranked month.

Sample data:

Month	Social Network	Gender	New followers
Jan-20	Instagram	Female	90
Jan-20	Facebook	Female	87
Jan-20	Snapchat	Female	71
Jan-20	Tiktok	Female	68
Jan-20	Tiktok	Male	99
Jan-20	Facebook	Male	96
Jan-20	Instagram	Male	95
Jan-20	Snapchat	Male	81
Feb-20	Snapchat	Female	69
Feb-20	Facebook	Female	46
Feb-20	Instagram	Female	45
Feb-20	Tiktok	Female	34
Feb-20	Facebook	Male	94
Feb-20	Instagram	Male	83
Feb-20	Tiktok	Male	31
Feb-20	Snapchat	Male	29
Mar-20	Facebook	Female	86
Mar-20	Instagram	Female	73
Mar-20	Tiktok	Female	55
Mar-20	Snapchat	Female	26
Mar-20	Instagram	Male	93
Mar-20	Tiktok	Male	92
Mar-20	Snapchat	Male	76
Mar-20	Facebook	Male	41

Excepted result:

Social Network	Gender	Best Rank	Month(Best Rank)	Folowers (best Rnk mth)
Facebook	Female	1	Mar-20	86
Facebook	Male	1	Feb-20	94
Instagram	Female	1	Jan-20	90
Instagram	Male	1	Mar-20	93
Tiktok	Female	3	Mar-20	55
Tiktok	Male	1	Jan-20	99
Snapchat	Female	1	Feb-20	69
Snapchat	Male	3	Mar-20	76

Thanks in advance

Regards,

Nithish

Re: Best Rank within category

Greg_Deckler — Wed, 06 May 2020 06:05:22 GMT

Seems like you need to do a RANKX and then a MINX or MAXX with appropriate filters.

Check out: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

and

See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Re: Best Rank within category

amitchandak — Wed, 06 May 2020 08:51:09 GMT

pbininja ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Re: Best Rank within category

pbininja — Wed, 06 May 2020 10:36:00 GMT

Hey,

Thanks for the replies Greg and Amit.

I am able to figure out the measure for the Rank. The problem is I am not able ascertain the best rank for the social networks and the month(best rank).

Please assist based on the sample data shared.

Thanks.

Regards,

Nithish