Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

CountIf in PowerBi

Hi All, I am stugging doing the same as below excel sheet in Power Bi. Please help me to create the Count IF's using PowerBi DAX. I have data colums : ID,  Count(s), Ratio. Please help me with the CO...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Use below DAX where you would need to replace Table with your table name and Field with your field name (i.e. this is $B$2:$B$654) and F2 with your value for which you want to count.

COUNTIF = 
COUNTROWS(
    FILTER(
        ALL(Table),
        Table[Field] = F2
    )
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Vijay, Thanks for yolur quick reply. I have data from past 3 years but COUNTIFS needed only for current year.In the filter if I am adding the year its not working corrcet. Please help me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I tried my DAX like Below for the Countif formula : =COUNTIFS($B$2:$B$654,">"&F4,$B$2:$B$654,"<="&G4)

     

    COUNTIF =
    COUNTROWS(
    FILTER(
    ALL('Query1'),
    'Query1'[$B$2:$B$654] > 'Query1'[&F4] && 'Query1'[$B$2:$B$654] <= 'Query1'[&G4] && 'Query1'[Year]=2022
    )
    )
     
    But Resultes are : 

     I used Don't summarize alos.

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Is $B$2:$B$654 a field name? What about F4 and G4...

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        My column names in PowerBi are different, I am just representing the column names in Excel.

        My DAX ; 

        COUNTIF =
        COUNTROWS(
        FILTER(
        ALL('Query1'),
        'Query1'[VN_OT] > 'Query1'[Lower Limit] && 'Query1'[VN_OT] <= 'Query1'[Upper Limit] && 'Query1'[VN Year]=2022
        )
        )