Forum Discussion

mushir1232's avatar
mushir1232
Regular Visitor
3 years ago

CountIf with Sequence Result

Hi All,

Need Assistance in Power Query  to DAX Measure to get the Sequential Count result on Countif.

I have applied : =COUNTIF($A$2:A2,A2)

 

 

Need Help

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, mushir1232 you'd probably want to keep sorting order so I added index column before transformation and removed it in the end. 

    let
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = <your_table>,
    sort_col = Table.AddIndexColumn(Source, "sort", 0, 1, Int64.Type),
    f = (tbl as table) as table => [a = Table.Sort(tbl, "sort"), b = Table.AddIndexColumn(a, "Result", 1, 1, Int64.Type)][b],
    g = Table.Group(sort_col, "Data", {{"all", each f(_)}}),
    all = Table.Combine(g[all]),
    sort_again = Table.Sort(all,{{"sort", Order.Ascending}}),
    remove_sort = Table.RemoveColumns(sort_again,{"sort"})
in
    remove_sort
    • mushir1232's avatar
      mushir1232
      Regular Visitor
      3 years ago

      Hi, thanks for your time to workout.

      What I Need a another Column in front of my Data showing the No of Counts . I want to know how many times the item is there in the Column with a sequence.

      If A is available in Different Row , in the given sample 4 times, so from the Top the It is counting and checking and providing the No of Counts from the Top always.

      Similar to COUNTIF($A$2:A2,A2) in Excel