topic Re: Top N Rank Dynamic Filter Using Field Parameters in DAX Commands and Tips

Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Mon, 25 Sep 2023 04:26:50 GMT

Hi all,

First time poster here. I've tried searching for answers all over but don't seem to be able to find the exact answer I was looking for. So hoping the DAX gurus here can pass provide some guidance.

I'm trying to create a dynamic Top N numeric parameter filter that filters for the Top N for each category, which is determined by field parameter, similar to the one shown in this video. What I want this to look like is as follows. These were created using the Top N filter in the filter pane (selected as top 5), by filtering either Primary Root Cause Level 0 or Primary Root Cause Level 1.

Here are the parameters that I have put in place:

I've followed the video and created this Rank formula in DAX:

Rank =

SWITCH(

TRUE(),

SELECTEDVALUE(ParameterDimension[ParameterDimension Fields])="'[Dashboard]'[Primary Root Cause Level 0]",

RANKX(

ALLSELECTED('[Dashboard]'[Primary Root Cause Level 0]),

[Count], , DESC)

SELECTEDVALUE(ParameterDimension[ParameterDimension Fields])="'[Dashboard]'[Primary Root Cause Level 1]",

RANKX(

ALLSELECTED('[Dashboard]'[Primary Root Cause Level 1]),

[Count], ,DESC)

)

However I notice that the Rank DAX formula filters for the Top N in the context of each month, rather than the whole period shown in the chart.

How can I write the correct DAX so that it replicates the behaviour of the static Top N filter dynamically?

Sorry for the long winded post. Hope I've illustrated my question clearly. Appreciate any help available.

Re: Top N Rank Dynamic Filter Using Field Parameters

lbendlin — Tue, 26 Sep 2023 17:38:40 GMT

Surround your RANKX statements with CALCULATE and add the Month filter modifier.

Re: Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Wed, 27 Sep 2023 00:36:51 GMT

Hi Ibendlin, thanks for your response.

So this is what I've done, but it's not changed anything to my result. I'm pretty sure there's something that I'm not doing right.

CALCULATE(

RANKX(

ALLSELECTED('[Dashboard]'[Primary Root Cause Level 0]),

[Count], , DESC),

FILTER(

ALL('Date-Reporting Dates'[Reporting Date]),

'Date-Reporting Dates'[Reporting Date])

)

'Date-Reporting Dates'[Reporting Date] is the X-axis.

I know I need to create the correct context for the rank formula, but I just don't know how to do it. Appreciate some further guidance on this.

Re: Top N Rank Dynamic Filter Using Field Parameters

lbendlin — Wed, 27 Sep 2023 13:20:58 GMT

You're nearly there.

var m = selectedvalue('Date-Reporting Dates'[Reporting Date]) return CALCULATE( RANKX( ALLSELECTED('[Dashboard]'[Primary Root Cause Level 0]), [Count], , DESC), 'Date-Reporting Dates'[Reporting Date]=m) )

Re: Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Thu, 28 Sep 2023 01:27:16 GMT

Thanks Ibendlin. I've updated the DAX, but nothing appears to have changed. Here's the full formula.

Rank = VAR _m=SELECTEDVALUE('Date-Reporting Dates'[Reporting Date]) Return SWITCH( TRUE(), SELECTEDVALUE(pBreachesField[pBreachesField Fields])="'[Dashboard] Incidents'[Primary Root Cause Level 0]", CALCULATE( RANKX( ALLSELECTED('[Dashboard] Incidents'[Primary Root Cause Level 0]), [Count], , DESC), 'Date-Reporting Dates'[Reporting Date]=_m) , SELECTEDVALUE(pBreachesField[pBreachesField Fields])="'[Dashboard] Incidents'[Primary Root Cause Level 1]", CALCULATE( RANKX( ALLSELECTED('[Dashboard] Incidents'[Primary Root Cause Level 1]), [Count], ,DESC), 'Date-Reporting Dates'[Reporting Date]=_m ) )

If it helps, this is the Dynamic Filter formula used to filter the chart.

Dynamic Filter Top N = VAR _selectednumber = SELECTEDVALUE('Top N'[Top N]) return IF( ISFILTERED('Top N'), INT( [Rank] <= _selectednumber ),1 )

Re: Top N Rank Dynamic Filter Using Field Parameters

lbendlin — Thu, 28 Sep 2023 01:32:02 GMT

Can you post a sample .pbix ?

If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Fri, 29 Sep 2023 03:55:20 GMT

Sample pbix

Not sure if this link will work given that it's a work OneDrive link.

Re: Top N Rank Dynamic Filter Using Field Parameters

lbendlin — Fri, 29 Sep 2023 14:18:23 GMT

close but it requires authentication. see if you can find a way to share publicly.

Re: Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Wed, 04 Oct 2023 03:49:07 GMT

Thanks mate. I'll have to check with my work admins to see how I can do that.

Re: Top N Rank Dynamic Filter Using Field Parameters

Anonymous — Mon, 13 Nov 2023 00:56:35 GMT

Hi there, bumping this again as I still haven't found a solution to this problem.

Unfortunately it doesn't appear possible for me to upload the workbook publicly. Perhaps I can illustrate the problem via the matrix visual.

Essentially I want to rank the Total in the bottom row for each category (Data, People etc.), but the Rank calculation is ranking in the context of each month (see Rank columns). Hoping to get the solution to his last missing piece to the calculation.