Forum Discussion

kaye123's avatar
kaye123
Frequent Visitor
3 years ago

TopN Based on Characters

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!

PowerQuery
TopN

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      kaye123
      Frequent Visitor
      3 years ago

      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. 

       

  • ERD's avatar
    ERD
    Community Champion
    3 years ago

    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"

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    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.