The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
I hope you can help me.
Thank you very much in advance
Solved! Go to 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.
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
Proud to be a Super User!
They are in decimal format so I want it to be >1 which is 24:00
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.