TopN Based on Characters

[kaye123]

I am trying to get only the last 4 quarters or last 13 weeks  that will be using for the Sparkline reference. My only reference of ranking is based on sorting it form highest to lowest. But since this are strings and not numbers, how can I use it using TopN DAX or other code function? Thanks for the help.

Quarters

2020_1

2020_2

2020_3

2020_4

Weeks 

2020_01

2020_02

Additional question:

I only want to extract my data from SQL server for every last 5 years in power query for faster loading and refresh. Any idea on this? Thanks!

[v-xiaotang]

Hi @kaye123 

>> I only want to extract my data from SQL server for every last 5 years in power query for faster loading and refresh. Any idea on this? Thanks!

you can set fitler on date column in Power Query Editor.

Best Regards,

Community Support Team _Tang

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

[ERD]

@kaye123 , you can

• write an SQL query with needed data and load it into the Power Query
• you can create a column in Power Query with transformed value converted to a number (2020_01 vs 202001) and use this new column for sorting, etc..

For the second option taking into account your column is text:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMog3MFRQitWBckDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [Column1.1] & Text.End( "0" & [Column1.2], 3 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2", "Column1.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}})
in
    #"Changed Type"

[amitchandak]

@kaye123 , Usually if number are sortable we can create rank and use that, In a separate table is best

example

new column in a separate week table

Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure example

Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[kaye123]

Thanks for this. I was able to create a table with showing specific rankings thanks for that. Now, I need this table to be flexible, in which ranking will depend on what i chose in the slicers or page filters. 

I created a measure like this below. but getting error on the scalar value issue. 

Top4Quarters =

Var tbl = SUMMARIZE(FiscalCalendar,FiscalCalendar[FiscalQuarter])

Var RNK_tbl = ADDCOLUMNS(tbl,"Rank",RANKX(tbl,[FiscalQuarter],[FiscalQuarter],DESC,Dense))

Return

    CALCULATE(RNK_tbl, FILTER(RNK_tbl,[Rank]>5))

I plan to use this as my x-axis on the sparklines. only showing last 4 quarters or last 13 weeks. depending on what year i choose from the slicer.