Conditional measure of another measure.

[Syndicate_Admin]

Hello I have a table in which there is a column with the employee cod another with the date and another with the hours performed, each month new data is added to each employee cod and what I want is to obtain the sum of the hours performed by each employee according to the sex and that are greater than 1 according to the period of time that is selected.

I have achieved this formula:

CALCULATE(MAXX('OWN AFFAIRS';SUM('OWN AFFAIRS'[Balance Asunt. Own]));FILTER('OWN AFFAIRS'; [Date]>=MINX('Calendar';' Calendar'[Date])&&[Date]<=[SELECTION DATE]&&'OWN AFFAIRS'[SEX]="H"))

I have achieved it but I need to only give me the sum of the hours that are greater than 1.

I attach an excel with the data and results I get

example

I hope you can help me.

Thank you very much in advance

[Watsky]

Hey  @patycnt,

Not sure how you have your hours data type setup, but try adding this to your filter

CONVERT( LEFT( 'OWN AFFAIRS'[horas], 1 ), INTEGER ) > 0

[Syndicate_Admin]

They are in decimal format so I want it to be >1 which is 24:00

[v-chenwuz-msft · Accepted solution]

Hi @Syndicate_Admin ,

1 You can just use the Filters and set the result of the masure is greater than 1, then apply.

2 the items ( hours ), used to sum, is greater than 1.

Add condition to the function filter()

if your [hours] type is 'Time', you can use hour to return the number of the hour. some measure like the followirng:

Measure = 
 MAXX(
        FILTER(
            'OWN AFFAIRS',
            [Date] >= MINX( 'Calendar', 'Calendar'[Date] )
                && [Date] <= SELECTEDVALUE( 'Calendar'[Date] )
                && 'OWN AFFAIRS'[sexo] = "H"
                && HOUR([horas])>1
            
        ),
        SUM('OWN AFFAIRS'[Balance Asunt. Own])
    )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.