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

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

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

         

         

         

        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.