COUNTIF in DAX with multiple expressions

[johnmelbourne]

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

[Mariusz]

Hi @johnmelbourne 

The below expresion should do the trick.

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

Hope this helps
Mariusz

[johnmelbourne]

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]

Hi @johnmelbourne 

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

Many Thanks

Mariusz

[johnmelbourne]

Hi,

Thanks for the follow up.

I want to filter this example data set into 5 groups and count those with values 0-5%, 6-10%, 11-15%, 16-20% and >25%, then filter by quarter.

So for example for Sep I would have 5 in the group 6-10%, and in December I would have 1.

The simple way for me to do it that I can think of is create 5 calculated columns and use an if statement with the result of 1 if true, then sum them in a 5 measures, but there is probably a neater solution.

Here is the data.

[Mariusz · Accepted solution]

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

[johnmelbourne]

Excellent solution and has broadened my knowledge.

Many thanks.

I had to round my percentage values to 2 digits, otherwise those percentages in between the ranges (say between 5 and 6, eg: 5.1 ended up not being captured and resulted in the catch all else >25 range, and also remove some of the equals to make everything suit my needs.

So ended up with

Column =

SWITCH(

   TRUE,

   ROUND(MyTable[Pct],2) >= 0 && ROUND(MyTable[Pct],2) <= 0.05, "0-5%",

   ROUND(MyTable[Pct],2) > 0.05 && ROUND(MyTable[Pct],2) <= 0.1, "6-10%",

   ROUND(MyTable[Pct],2) > 0.10 && ROUND(MyTable[Pct],2) <= 0.15, "11-15%",

   ROUND(MyTable[Pct],2) > 0.15 && ROUND(MyTable[Pct],2) <= 0.2, "16-20%",

   ROUND(MyTable[Pct],2) > 0.2 && ROUND(MyTable[Pct],2) <= 0.25, "21-25%",

   ">25%"

)

Regards

John