Sum the line items based on condition

[Anonymous]

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. 

[johnt75 · Accepted solution]

Try

Total Days =
SUMX ( 'Table', IF ( 'Table'[Hours] > 0, 1 ) )

[AlexisOlson]

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 )

[Anonymous]

Hey @johnt75  Thank you so much for quick revert. The code helped me to solve the problem. 

[Anonymous]

@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.  

[johnt75]

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

[Anonymous]

So we should not create measure for such a case?  

[johnt75]

I wouldn't, no. A measure is typically an aggregation of a value, e.g. the sum or the average. You can't aggregate a country, and typically you would want to use such a thing to slice or filter some measures. Better to have something like that in a column.