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

[rita21fonseca]

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!

[Anonymous]

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!

[rita21fonseca]

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.

[Rupak_bi]

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

[Rupak_bi]

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)

    )

)

[rita21fonseca]

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

[Rupak_bi]

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)

    )

)