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

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

    • Dan27's avatar
      Dan27
      Regular Visitor
      8 years ago

      Hi Raj

       

      Thanks again for your help but again this isn't getting me the desired output because it's not performinng the count part.

       

      I'm wanting to return a count of occurrences within a range. So my Excel equivalent would be

       

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

       

      In Power BI this function doesn't work. I'm unsure if I can nest a COUNT and IF formula but so far what I've tried hasn't worked.

       

      Thanks

      Dan