topic Re: How to use a dynamic filter to get only the top n values in a bar chart in DAX Commands and Tips

How to use a dynamic filter to get only the top n values in a bar chart

rita21fonseca — Thu, 06 Jun 2024 09:27:26 GMT

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:

TOPN_VALUES=

VAR SelectedTop = SELECTEDVALUE([TOPN], 10)

RETURN

SWITCH(

TRUE(),

SelectedTop = 0,

CALCULATE(

SUM([hours]),

FILTER("my_table", [status] = True)

CALCULATE(

SUM([hours]),

KEEPFILTERS(TOPN(

SelectedTop,

ALLSELECTED([name]),

SUMX('my_table', [hours]),

DESC

)),

FILTER('my_table', [status] = True)

)

Thanks for the help!

Re: How to use a dynamic filter to get only the top n values in a bar chart

Rupak_bi — Thu, 06 Jun 2024 10:28:22 GMT

Hi ,

Make a slicer of PARAM_TOP and modify the measure as below

TOPN_VALUES=

VAR SelectedTop = Max(Table,PARAM_TOP)

RETURN

SWITCH(

TRUE(),

SelectedTop = 0,

CALCULATE(

SUM([hours]),

FILTER("my_table", [status] = True)

CALCULATE(

SUM([hours]),

KEEPFILTERS(TOPN(

SelectedTop,

ALLSELECTED([name]),

SUMX('my_table', [hours]),

DESC

)),

FILTER('my_table', [status] = True)

)

Re: How to use a dynamic filter to get only the top n values in a bar chart

rita21fonseca — Thu, 06 Jun 2024 11:19:01 GMT

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

Re: How to use a dynamic filter to get only the top n values in a bar chart

Anonymous — Fri, 07 Jun 2024 02:28:11 GMT

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!

Re: How to use a dynamic filter to get only the top n values in a bar chart

Rupak_bi — Fri, 07 Jun 2024 07:53:14 GMT

OK,

Try this. I am considering your individual calculate logic is working fine. If you still get some error, plz share sample data

TOPN_VALUES=

VAR SelectedTop = Max(Table,PARAM_TOP)

RETURN

If(

SelectedTop = 0,

CALCULATE(

SUM([hours]),

FILTER("my_table", [status] = True)

CALCULATE(

SUM([hours]),

KEEPFILTERS(TOPN(

SelectedTop,

ALLSELECTED([name]),

SUMX('my_table', [hours]),

DESC

)),

FILTER('my_table', [status] = True)

)

Re: How to use a dynamic filter to get only the top n values in a bar chart

rita21fonseca — Wed, 12 Jun 2024 15:58:51 GMT

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.

Re: How to use a dynamic filter to get only the top n values in a bar chart

Rupak_bi — Thu, 13 Jun 2024 08:43:11 GMT

Please share sample data and modelling. then only it can be tried.