Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count IF Dax Formula

Hello, I'm trying to count the number of lawyers ( distinct timekeeper ID's) that have more than 500 attorney hours.     My graph below shows the count of all distinct timekeeper ID's, but I would ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Try like 

    countX(filter(summarize(Table[TimeKeeper ID],"_1",sum(Table[Hour])),[_1]>=500),[TimeKeeper ID])

Tahreem24's avatar
Tahreem24
Super User
6 years ago

Anonymous ,

Try the below DAX:

Unique ID = CALCULATE(DISTINCTCOUNT(Table[TimeKeeper ID]),Table[Hour]>=500)

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Anonymous ,

    I apologize, please use the corrected one:

    Unique ID = CALCULATE(DISTINCTCOUNT(Table[TimeKeeper ID]),FILTER(Table,SUM(Table[Hour])>=500))

    I have created the below measure as per your scenario but I put 1000000 as a threashold value instead of 500.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Tahreem24 My numbers did not change b/c I believe it's filtering the entire table when sum of all timekeeper ID's hours are greater than 500.  The table has many records of the same timekeeper ID's.  i.e. one timekeeper ID for each month (or 12 records for one timekeeper in 2019).  So, I need the measure to count the distinct timekeeper ID's when the sum of hours for each timekeeper id is greater than 500.  Does that make sense?

       

      Below was my attempt: