Forum Discussion
Sum the line items based on condition
Try
Total Days = SUMX ( 'Table', IF ( 'Table'[Hours] > 0, 1 ) )
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.
I wouldn't do that as a measure, I would do it as a calculated column.
The easiest way would be to use Power Query to add a conditional column and use the "begins with" operator.
You could also use Power Query to create a new column by extracting the first N characters from the employee code. You could then create a table containing all the countries and the digit codes and link them in a one-to-many relationship which you could use as a slicer or filter
