Forum Discussion
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 like to create a measure that counts only the distinct timekeeper ID's when the sum of each Timekeeper Id's Atty hours are greater than 500. Note the data has many rows with the same TK'd and various amounts of billable hours for the same TK ID. So, I'm looking to count distinct timekeeper ID's when the aggregate sum of billable hours is greater than 500. I'd appreciate any help! Thanks.
Anonymous , Try like
countX(filter(summarize(Table[TimeKeeper ID],"_1",sum(Table[Hour])),[_1]>=500),[TimeKeeper ID])
6 Replies
Anonymous ,
Try the below DAX:
Unique ID = CALCULATE(DISTINCTCOUNT(Table[TimeKeeper ID]),Table[Hour]>=500)
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.
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:
Anonymous , try
countX(values(Table[TimeKeeper ID]),if(sum(Table[Hour])>=500,[TimeKeeper ID],blank()))
countX(filter(summarize(Table[TimeKeeper ID],"_1",sum(Table[Hour])),[_1]sum(Table[Hour])),[TimeKeeper ID])
amitchandakassuming these are two different options. I tried the first but got the same results:
countX(values(Table[TimeKeeper ID]),if(sum(Table[Hour])>=500,[TimeKeeper ID],blank()))
I gotet the following error with the second formula:
Anonymous , Try like
countX(filter(summarize(Table[TimeKeeper ID],"_1",sum(Table[Hour])),[_1]>=500),[TimeKeeper ID])
