Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone!
I currently have a filter TOP N in a dashboard where the user can choose which top values he wants to see in the visuals of the board. The field of the filter is an attribute called PARAM_TOPN which is a column in a table that goes from 1 to 20 - What I was hoping it would do is to allow the user to choose between the top 1 values and the top 20.
However, when I create the visuals, I'm not able to filter the top values... I don't know if the problem is with the way that I created the filter or with the way that I tried to utilize the filter in the DAX query... As an example of how I'm trying to filter, the DAX query (the measure I created) for a bar chart that is supposed to show the top n values (but instead shows every single value) is as follows:
Hi @rita21fonseca ,
Thanks for the reply from Rupak_bi .
The TOPN function works by ranking a table based on a given expression.
The TOPN function is very context dependent and I can't solve the problem based on the less information you provided.
Here is my modification of your measure syntax:
TOPN_VALUES =
VAR SelectedTop = SELECTEDVALUE('my_table'[TOPN], 10)
RETURN
SWITCH(
TRUE(),
SelectedTop = 0, CALCULATE(
SUM('my_table'[hours]),
FILTER('my_table', 'my_table'[status] = TRUE)
),
CALCULATE(
SUM('my_table'[hours]),
KEEPFILTERS(
TOPN(
SelectedTop,
ALL('my_table'[name]),
SUMX('my_table', 'my_table'[hours]),
DESC
)
),
FILTER('my_table', 'my_table'[status] = TRUE)
)
)
I just tried replacing the ALLSELECTED function with the ALL function.
I would be grateful if you could provide me with the pbix file or sample data.
Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hello @Anonymous !
Unfortunately, neither your solution nor @Rupak_bi 's worked...
My dataset is too big and the Powerbi project is a bit heavy to pass on, but essentially I'm working with a star schema database model between records(fact table) and processes(dimension table).
In this excerpt, my variables are as follows:
TOPN > a column from an auxiliary table that doesn't have any relationship with the star schema, and that contains the values between 1 and 20 so that I can select which "top n" I want to select
hours > the number of hours the record has executed (in the fact table)
status > the status of the record (in the fact table)
name > the name of the process (in the dimension table)
So basically I want to get a column graph of the top n processes (name) according to the execution time.
Please share sample data and modelling. then only it can be tried.
Hi ,
Make a slicer of PARAM_TOP and modify the measure as below
Hello @Rupak_bi !
Thank you so much for your answer!
I tried your solution, however I'm getting the following error:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
OK,
Try this. I am considering your individual calculate logic is working fine. If you still get some error, plz share sample data
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |