Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Countif in power bi
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular zuriqi's Avatar
    Join Date
    Dec 2008
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countif in power bi

    Hi

    I am looking for a similar to the below formula to find the count of item in each and every row in the entire column.

    =COUNTIF($A$1:A2,A2)

    =COUNTIF(Table1[[#Headers],[ColA]:[ColA]]:[@ColA],[@ColA])

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    Are you trying to count the number of items, or get a total of the values in the cells? =SUMIFS will do a better job of totaling the value of the cells.

  3. #3
    Board Regular zuriqi's Avatar
    Join Date
    Dec 2008
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    No actually i want to create a new column. in that column, i want to count the repeated cell values in the previous column by locking the first cell while counting as shown below. I can do this in excel tables but i want to do it in power bi.

    ColA ColB
    AA1 1
    AA2 1
    AA1 2
    AA4 1
    AA5 1
    AA2 2
    AA7 1
    AA8 1
    AA1 3
    AA4 2


    Quote Originally Posted by treaves04 View Post
    Are you trying to count the number of items, or get a total of the values in the cells? =SUMIFS will do a better job of totaling the value of the cells.

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    Hi,

    Have a look at Ken's article here:

    https://www.excelguru.ca/blog/2018/0...g-power-query/

    Cheers,

    Matty

  5. #5
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    It was a little bit tricky but following query works as expected:

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Index_1 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Sorted_Lines = Table.Sort(Index_1,{{"ColA", Order.Ascending}, {"Index", Order.Ascending}}),
    Index_2 = Table.AddIndexColumn(Sorted_Lines, "Index-2", 1, 1),
    Index_3 = Table.AddIndexColumn(Index_2, "Index-3", 0, 1),
    Userdefinded_Col = Table.AddColumn(Index_3, "Count_from", each if[#"Index-3"] = 0 or [ColA] <> Index_3[ColA]{[#"Index-3"]-1} then [#"Index-2"] else null),
    Fill_down = Table.FillDown(Userdefinded_Col,{"Count_from"}),
    Column_B = Table.AddColumn(Fill_down, "Col-B", each [#"Index-3"] - [Count_from] + 2),
    Sort_by_original_order = Table.Sort(Column_B,{{"Index", Order.Ascending}}),
    Remove_all_help_columns = Table.RemoveColumns(Sort_by_original_order,{"Index", "Index-2", "Index-3", "Count_from"})


    in
    Remove_all_help_columns

  6. #6
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    I tested the query with 11000 lines and had to wait almost 7 minutes to get the result.


    So you can probably see that queries that use an index to access other lines are not one of Power Query's strengths at the moment.

  7. #7
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    If I use Table.Buffer, the runtime of 11,000 records is reduced to about 50 seconds. This is much better than 7 minutes, but still not good enough

    Code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Index_1 = Table.Buffer(Table.AddIndexColumn(Source, "Index", 0, 1)),
    Sorted_Lines = Table.Sort(Index_1,{{"ColA", Order.Ascending}, {"Index", Order.Ascending}}),
    Index_2 = Table.AddIndexColumn(Sorted_Lines, "Index-2", 1, 1),
    Index_3 = Table.Buffer(Table.AddIndexColumn(Index_2, "Index-3", 0, 1)),
    Userdefinded_Col = Table.AddColumn(Index_3, "Count_from", each if[#"Index-3"] = 0 or [ColA] <> Index_3[ColA]{[#"Index-3"]-1} then [#"Index-2"] else null),
    Fill_down = Table.FillDown(Userdefinded_Col,{"Count_from"}),
    Column_B = Table.AddColumn(Fill_down, "Col-B", each [#"Index-3"] - [Count_from] + 2),
    Sort_by_original_order = Table.Sort(Column_B,{{"Index", Order.Ascending}}),
    Remove_all_help_columns = Table.RemoveColumns(Sort_by_original_order,{"Index", "Index-2", "Index-3", "Count_from"})
    in
    Remove_all_help_columns

  8. #8
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    Hello,


    since my solution produces correct results, but I was not satisfied with the runtime, I asked on excelguru for a better solution and got there two solution variants, which are many faster and have extended my PQ horizon enormously.


    Here is the link

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,329
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Countif in power bi

    Quote Originally Posted by zuriqi View Post
    Hi

    I am looking for a similar to the below formula to find the count of item in each and every row in the entire column.

    =COUNTIF($A$1:A2,A2)

    =COUNTIF(Table1[[#Headers],[ColA]:[ColA]]:[@ColA],[@ColA])
    Does the sort order matter?

    ColA Counter
    AA1
    1
    AA1
    2
    AA1
    3
    AA2
    1
    AA2
    2
    AA4
    1
    AA4
    2
    AA5
    1
    AA7
    1
    AA8
    1
    Last edited by sandy666; Jun 23rd, 2019 at 03:28 PM. Reason: Table added
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    Yes, the original order should be retained at the end. Both my and the linked solutions take this into account

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •