Top N Rank Dynamic Filter Using Field Parameters

Question

Hi all,&nbsp;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.&nbsp;&nbsp;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.&nbsp;Here are the parameters that I have put in place:&nbsp;I've followed the video and created this Rank formula in DAX:Rank =SWITCH(&nbsp; &nbsp; TRUE(),&nbsp; &nbsp; SELECTEDVALUE(ParameterDimension[ParameterDimension Fields])="'[Dashboard]'[Primary Root Cause Level 0]",&nbsp; &nbsp; RANKX(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('[Dashboard]'[Primary Root Cause Level 0]),&nbsp; &nbsp; &nbsp; &nbsp; [Count], , DESC)&nbsp; &nbsp; &nbsp; &nbsp; ,&nbsp; &nbsp; SELECTEDVALUE(ParameterDimension[ParameterDimension Fields])="'[Dashboard]'[Primary Root Cause Level 1]",&nbsp; &nbsp; RANKX(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('[Dashboard]'[Primary Root Cause Level 1]),&nbsp; &nbsp; &nbsp; &nbsp; [Count], ,DESC)&nbsp; &nbsp; )&nbsp;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.&nbsp;&nbsp;How can I write the correct DAX so that it replicates the behaviour of the static Top N filter dynamically?&nbsp;&nbsp;Sorry for the long winded post. Hope I've illustrated my question clearly. Appreciate any help available.&nbsp;

lbendlin · Answer

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

anonymous · Answer

Hi Ibendlin, thanks for your response.&nbsp;&nbsp;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.&nbsp;&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RANKX(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('[Dashboard]'[Primary Root Cause Level 0]),&nbsp; &nbsp; &nbsp; &nbsp; [Count], , DESC),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('Date-Reporting Dates'[Reporting Date]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date-Reporting Dates'[Reporting Date])&nbsp; &nbsp; )&nbsp;'Date-Reporting Dates'[Reporting Date] is the X-axis.&nbsp;&nbsp;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.&nbsp;

lbendlin · Answer

You're nearly there.&nbsp;&nbsp;
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)
    )
&nbsp;

anonymous · Answer

Thanks Ibendlin. I've updated the DAX, but nothing appears to have changed. Here's the full formula.&nbsp;&nbsp;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
    )
)&nbsp;&nbsp;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] &lt;= _selectednumber
),1
)&nbsp;

lbendlin · Answer

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

Forum Discussion

Top N Rank Dynamic Filter Using Field Parameters

9 Replies

Related content

Dynamic Filter

Dynamic Filtering

How to filter a slicer dynamically based on selection on field parameter

Dynamic measure filtering

Dynamic subcription parameter not saving