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(  ...
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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

COUNTIFS in Excel are generally replaced by using CALCULATE.

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

    • Dan27's avatar
      Dan27
      Regular Visitor
      8 years ago

      Thanks Raj

       

      What you've put is really helpful but it still won't work.

       

      I 'm trying to work out where if Field or column 'G' is less than or equal to column 'H' then return the values of column 'AL' else return a " " blank.

       

      I've tried a few variations of what you suggested but none work. Where above I've referenced column letters I have replaced these with (Table[field]) accordingly.

       

      Thanks

      Dan

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        :)

         

        That was the example of CALCULATE.

         

        Here is teh syntax for your Question:

         

        Calculated_Column=IF(Table1[ColumnG] <= Table1[ColumnH] , Table1[ColumnAL], BLANK()

         

        If you want count.

         

        Cnt_Measure = COUNT(Table1[Calculated_Column])

         

        Thanks

        Raj