Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
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.
Surround your RANKX statements with CALCULATE and add the Month filter modifier.
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.
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)
)
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
)
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-...
Not sure if this link will work given that it's a work OneDrive link.
close but it requires authentication. see if you can find a way to share publicly.
Thanks mate. I'll have to check with my work admins to see how I can do that.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |