Forum Discussion

atharvaunde59's avatar
atharvaunde59
Frequent Visitor
6 years ago
Solved

COUNTIFS() with comparison

Hello everyone,

am extremly sorry for posting for this problem again though there are lot of similar topic threads are there.

I have a table with multiple columns

I want to use countifs() in it but as powerbi is not having support for it 

in excel i was using result=countif(<range>,"<20") to get count of rows who had values less than 20.

How it can be acheived in DAX?

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi atharvaunde59 

     

     

    Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table', 'Table'[Range] < 20))

     

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Try this measure

    Measure = CALCULATE(COUNTROWS('Table'),'Table'[Range] < 20)

8 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi atharvaunde59 

     

     

    Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table', 'Table'[Range] < 20))

     

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

    • atharvaunde59's avatar
      atharvaunde59
      Frequent Visitor
      6 years ago

      I tried that measure but its is giving me wrong output.

      I edited it as follwing 

      Measure = CALCULATE(COUNTROWS('sppu_se_comp sem_1_analysis'),FILTER('sppu_se_comp sem_1_analysis', 'sppu_se_comp sem_1_analysis'[COA_TH] < 20))
       
       
      where sppu_comp sem_1_analysis is my table name and COA_TH is column name from which I need to count no of students who's marks are less than 20.
       
      To verify it I applied filter on excel data and the actual answer is 8 but when i add that measure to card it shows count as 1
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Try this measure

    Measure = CALCULATE(COUNTROWS('Table'),'Table'[Range] < 20)

    • atharvaunde59's avatar
      atharvaunde59
      Frequent Visitor
      6 years ago

      Thanks  a lot,

      got it solved. Can u help me regarding my issues in plotting spider chart. May I DM u?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

        You are welcome.  If my reply helped, please mark it as Answer.  If you second question is related to this, post it here, else please start a new thread.