Create Dynamic Lists of Dimensions using DAX in Power BI

by | Power BI

Have you ever wanted to showcase the top three, five, or ten best results across a particular dimension? You may watch the full video of this tutorial at the bottom of this blog.

Well, in this video I’m going to show you exactly how you can do that with a specific example.

The example we’re going to work through is one where I want to find my top three salespeople automatically across any region in my data set. I also want to be able to drill into any particular state and analyse just the stores in those states and the salespeople that work across those particular stores.

So, a really, really powerful insight. You could use this in many ways. You may want to reward your top salespeople. In the past, getting all that information out may have taken a long time.

In this case, what you can do is you can build the internal logic into your calculations and then very quickly and dynamically drill into that specific insight. You can go across any time frame because we can utilize the data model that we have built-in behind the scenes.

Maybe you want to reward people. Or maybe you want to look at your bottom three. Or maybe you want to change the number of top or bottom salespeople based on the ranking that you’re actually looking at.

All of these analytical opportunities are available to you. It’s exactly the same technique.

All you have to do is rework a few of the parameters that you input into the formula and then you can enable a different type of insight. It’s a similar technique that delivers a slightly different result.

I’ve used this in multiple different models and reports that I’ve developed and find that in terms of data extraction and the speed to extract this sort of insight, it’s just second to none.

Being able to dynamically review any specific time period and then extract a list of any dimension in a dynamic way just saves so much time and enables so many different types of great analysis that you can apply to your own data sets.

Good luck with this one.


Related Posts