Forum Discussion

johnmelbourne's avatar
johnmelbourne
Icon for Helper V rankHelper V
7 years ago
Solved

COUNTIF in DAX with multiple expressions

 

Hi,

 

So I want to count the number of rows in a table where the column value in the table is between two ranges.

In Excel it is like =COUNTIFS(A1:A50,">=1", A1:A50,"<=10")

 

how do I do this in powerbi?

 

I have played around with CountX, Countrows, filters .... it is currently beyond me.

 

Thanks for any advice.

John

 

 

 

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi johnmelbourne 

     

    You can add a column and use the expresion below.

    Column = 
SWITCH(
    TRUE,
    Table1[pct] >= 0 && Table1[pct] <= 0.05, "0-5%",
    Table1[pct] >= 0.06 && Table1[pct] <= 0.1, "6-10%",
    Table1[pct] >= 0.11 && Table1[pct] <= 0.15, "11-15%",
    Table1[pct] >= 0.16 && Table1[pct] <= 0.2, "16-20%",
    Table1[pct] >= 0.2 && Table1[pct] <= 0.25, "20-25%",
    ">25%"
)



    Hope this helps
    Mariusz

6 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi johnmelbourne 

    The below expresion should do the trick.

     

     

    Measure = 
COUNTROWS(
    FILTER(
        YourTable,
        YourTable[YourField] >= 1 && YourTable[YourField] <= 10
    )
)

    Hope this helps
    Mariusz

     

    • johnmelbourne's avatar
      johnmelbourne
      Icon for Helper V rankHelper V
      7 years ago
      Thanks, that is cool however it seems to be ignoring the slicer data (filter context?). Something is not right as it is not producing correct numbers and I cant figure out why. I am trying to filter by a specific quarter selected in a slicer. Any clues?
      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi johnmelbourne 

        Would you mind to explain in more detail what you are looking to achieve?

        Many Thanks

        Mariusz