Dynamic Top N slicer based on a measure for building a Line Chart

[Anonymous]

I have a table "Tags" that looks like this:

IDTags	IDUser	Name	isTrue	Date
1	1	Python	True	01/01/2020
2	10	C	False	01/05/2020
1	100	Python	False	01/06/2020
1	1000	Python	True	01/01/2021
2	110	C	True	01/01/2021
3	11	Java	False	01/03/2021

I want to rank the number of times each "Name" appears in the table in order to filter the Top N through a dynamic slicer in a cumulative line chart. Date is linked to a Calendar table and IDUser to a User dimension

So far I have those measures:

_m_get_tags_rank = 
RANKX (
    ALLSELECTED ( 'Tags'[Name] ),
    COUNTA( 'Tags'[IDTag] ),
    ,
    DESC,
    DENSE
)

_m_is_top_selected = 
VAR SelectedTop = SELECTEDVALUE('TOP N'[Value])
RETURN
IF (
    [_m_get_tags_rank] <= SelectedTop,
    1,
    BLANK()
)

_m_cumulative_number_of_tags = 
CALCULATE (
    COUNTA( 'Tags'[IDTag] ),
    FILTER (
        ALL ( 'DimDate' ),
        'DimDate'[CalendarDate] <= MAX ( 'DimDate'[CalendarDate] )
    )
)

And a table for the slicer

TOP N = GENERATESERIES(0, 50, 5)

I use _m_is_top_selected to filter the 1s. This works when I show the information in a tabular visualization, but not when I create the cumulative line chart with "Name" as the legend and the Calendar table as the axis. I guess it has to do with the fact that they are not columns, so it cannot understand which rows should appear as 1 and which as blank.

I have thought on creating a similar solution using calculated columns, but it wouldn't work since my dashboard contains other slicers and filters, based, for example, in the User dimension.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your question, the _m_get_tags_rank formula you created cannot be sorted correctly. I did the following test: Change the formula as shown in the figure below, and then click _m_get_tags_rank to sort in descending order. Through the Top N filter, the value that satisfies 1 can be correctly filtered.

_m_get_tags_rank = 
CALCULATE (
    COUNTROWS ( Tags ),
    FILTER ( ALL ( Tags ), Tags[IDTags] = MAX ( Tags[IDTags] ) )
)

_m_is_top_selected = 
VAR SelectedTop = SELECTEDVALUE('TOP N'[Value])
RETURN
IF (
    [_m_get_tags_rank] <= SelectedTop,
    1,
    BLANK()
)

If the problem is still not resolved, please provide detailed error information and data model. Let me know the result immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Hi @amitchandak ! thanks for your answer.

When I was developing my solution, that was one of the articles I used as a reference. What I have so far is able to do the same thing as the article in a tabular visualization. Unfortunatelly it doesn't work in a line chart.

I have spoken with the user and he apparently accepts to use the filters on the right to edit the "N" for the Top filter. Anyway, I would like to find a proper way to solve this.

Thank you!