Forum Discussion

Dan27's avatar
Dan27
Regular Visitor
8 years ago
Solved

COUNTIFS in Power BI

Hello all,

 

Hoping someone can help. I'm trying to recreate the below excel formula in Power BI

 

=COUNTIFS(G:G,"<="&H:H,AL:AL,"")

 

I've tried combinations of 

 

Count = COUNTROWS(FILTER( 

 

I've referred to my field names rather than the column letter etc but still can't get it to work. How can I do he formula above but of course subsitute column names for the fields.

 

Thanks

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Dan27,

     

    Based on this sample data, is 3 the desired result? If so, please new a measure and add it to card visual.

    Measure1 = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[G]<=Table2[H]))

     

    For more advice, please illustrate your requirement with actual sample data and desired result rather than just the Excel functions.

     

    Regards,

    Yuliana Gu

8 Replies

    • Dan27's avatar
      Dan27
      Regular Visitor
      8 years ago

      Thanks Greg but in what format?

       

      If I use FieldName = CALCULATE([Field1] <= [Field2], " ") for example then I get a message saying the maximum argument count is 2. 

       

      Appreciated. Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hereis the example.

         

        CALCULATE ( COUNT(Table1[Column1]), FILTER ( Table1, Table1[column1] < Table1[Clumn2]))

         

         

        Thanks
        Raj

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Dan27,

     

    Based on this sample data, is 3 the desired result? If so, please new a measure and add it to card visual.

    Measure1 = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[G]<=Table2[H]))

     

    For more advice, please illustrate your requirement with actual sample data and desired result rather than just the Excel functions.

     

    Regards,

    Yuliana Gu

  • NataliG's avatar
    NataliG
    New Member
    2 years ago

    Hi,

     

    I've gone through many posts on this topic and unfortunately nothing seems to work. Maybe my knowledge of DAX is limited and i'm not able to customise the solutions as per my needs so eager to receive any help.

     

    I'm trying to identify duplicate booking of purchase invoices in my dataset which is something like the table below. I need to be able to use a countifs like functionality to check for rows where invoice no# and transaction amount are the same.  so in excel i would have added a column after D with the following formula :COUNTIFS(C:C,C2,D:D,D2). And then i could proceed to evaluate any rows that returned a value greater than 1. I have not been able to achieve this yet in power bi. any help is greatly appreciated.

     

    ABCD
    Vendor ID.#NameInvoice No.# Transaction Amount 
    600754aaaa123366
    989111bbbb234320
    242001cccc86030242
    600754aaaa123366