Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Top N Rank Dynamic Filter Using Field Parameters

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 ...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
lbendlin
Super User
2 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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
)