Top N Rank Dynamic Filter Using Field Parameters

[JoeX]

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. 

[JoeX]

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. 

[lbendlin]

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

[JoeX]

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. 

[lbendlin]

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)
    )

[JoeX]

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
)

[lbendlin]

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-...

[JoeX]

Sample pbix 

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

[lbendlin]

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

[JoeX]

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