Top N filter type in page level

[EZiamslow]

Hi,
I have been trying to find a solution to this problem since I started Power BI 1 year ago. Please help me with the best solution for me.

I have 6 visuals and using the date table below. My x-axis is FYWW_C_PQ . I have FYWW_C_PQ column on the filter for each visuals Top N set to Top = 24 to see the last 24 weeks and including the current week. Is it possible to use Top N on the page level? If I want to see 48 weeks, I have to adjust all my visuals individually. How would you solve this issue?

I already tried creating a calculated columns

" TopN = RANKX('Calendar_MC', 'Calendar_MC'[Index WW],,DESC,Dense) ." However, Top N is still not available. Is it not available in Power BI for Top N filter in page level?

If I use Date column and use the relative date filter to calendar weeks, the problem with that is it does not include current week.

Please help me out. Thanks.

let
    today = Date.From( DateTime.LocalNow() ),
    start = Date.AddYears( today, -2 ),
    Custom1 = List.Dates(start, Number.From( today )- Number.From( start ), #duration(1,0,0,0)),
    #"Sorted Items" = List.Sort(Custom1,Order.Ascending),
    #"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "FY4", each "FY"& (if Date.Month([Date])<= 6 then Number.ToText( Date.Year([Date])) else Number.ToText(Date.Year([Date])+1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WW", each "WW" & (if Date.WeekOfYear([Date])-27+1<=0 then Number.ToText(52+Date.WeekOfYear([Date])-27+1) else Number.ToText( Date.WeekOfYear([Date])-27+1))),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Date", Order.Descending}}),
    #"Inserted First Characters" = Table.AddColumn(#"Sorted Rows", "First Characters", each Text.Start([FY4], 2), type text),
    #"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End([FY4], 2), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FY"),
    #"Added Conditional Column" = Table.AddColumn(#"Merged Columns", "Custom", each if [WW] = "WW1" then "WW01" else if [WW] = "WW2" then "WW02" else if [WW] = "WW3" then "WW03" else if [WW] = "WW4" then "WW04" else if [WW] = "WW5" then "WW05" else if [WW] = "WW6" then "WW06" else if [WW] = "WW7" then "WW07" else if [WW] = "WW8" then "WW08" else if [WW] = "WW9" then "WW09" else [WW]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"FY4", "WW"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"FY", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"FYWW_C_PQ"),
    #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Date", Order.Descending}})
in
    #"Sorted Rows1"

[v-deddai1-msft · Accepted solution]

Hi @EZiamslow ,

 In addition to Greg's reply, TopN filter is a visual level filter. And you can add a calculated column for week numbers away from today:

weeknum = DATEDIFF('Table'[Date],TODAY(),WEEK)

Add 'weenum' column to the page level filter and set it is less than 24.

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

Best Regards,

Dedmon Dai

[Greg_Deckler]

No, I do not believe TopN filter is available at page level. Not sure why you would need it though. Just create a week number column that is something like Week Num Relative = WEEKNUM(TODAY()) - WEEKNUM([Date]). Then just add it is an Advanced filter less than 24.