Forum Discussion
Sum the line items based on condition
Hi everyone, I'm trying to write a formula for the conditions below. Someone please help me write a code for the condition.
Here for the number of days, I'm want to calculate no of days based on the condition that ---> Hours > 0, then 1 day of 1 date line item.
Here I tried using IF conditon -
IF(HOURS) > 0, 1
But the problem with code is that, it's not summing up. I want all the 1's to add up and give me no of days in total.
Somebody please help me to correct this code.
Thanks in advance.
Try
Total Days = SUMX ( 'Table', IF ( 'Table'[Hours] > 0, 1 ) )
7 Replies
Try
Total Days = SUMX ( 'Table', IF ( 'Table'[Hours] > 0, 1 ) )This solution works fine but, in general, DAX is more efficient with filtering operations than evaluating IF on every iteration. A bit more detail here: https://blog.enterprisedna.co/iterators-and-context-transitions-in-dax-queries/
You could rewrite this using filtering like this:
SUMX ( FILTER ( 'Table', 'Table'[Hours] > 0 ), 1 )Or even something like this:
CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Hours] > 0 )Hey johnt75 Thank you so much for quick revert. The code helped me to solve the problem.
johnt75 one more help, you can see at the left you can see Employee ID. I want to write a DAX measure code to derive country. For ex: If employee ID starts with 1, then UK. If employee code starts with 2 then US. Can you please help out?
I have done it in Excel but finding difficult with DAX.
