topic Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs in DAX Commands and Tips

Summing up of manhours based on slicer range selection corresponding to filtered jobs

ankurbajaj07 — Fri, 04 Apr 2025 08:12:49 GMT

In power bi, I need a measure for dax code where when user selects date range from a slicer (calender table), jobs (only the instances where it lies in the selected calender date range) and corresponding manhours (only for the selected calender date range) from my table Bi_ alloceffort_data gets displayed where the REVDATE in same table falls between the user selected range.

I tried the follwomg codes:

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
CALCULATE(
SUM(Bi_alloceffort_data[Manhours]),
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
)

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
SUMX(
FILTER(
Bi_alloceffort_data,
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
),
Bi_alloceffort_data[Manhours]
)

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
CALCULATE(
SUM(Bi_alloceffort_data[Manhours]),
KEEPFILTERS(
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
))

But all the solutions are selecting the job based on slcier filter and then summing all the manhours (even ehen they are outside the slicer range) for the all job instances.

I need manhours only for the instance where jobs and manhours are within the selected slicer range.

Pls help

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

bhanu_gautam — Fri, 04 Apr 2025 08:38:47 GMT

ankurbajaj07 , Try using

dax
Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
SUMX(
FILTER(
Bi_alloceffort_data,
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
),
Bi_alloceffort_data[Manhours]
)

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

ankurbajaj07 — Fri, 04 Apr 2025 09:04:56 GMT

Its the same as option 2 which i tried but is not filtering manhours based on seletcion. When i am using this measure in table along with jobs, only job are getting filtered in line with slcier and then manhours are getting summed up against that job for all instances of the job removing the slicer criteria.

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

techies — Fri, 04 Apr 2025 12:38:19 GMT

Hi ankurbajaj07 please check this

Selected Manhours =

CALCULATE (

SUM ( Bi_alloceffort_data[Manhours] ),

FILTER (

Bi_alloceffort_data,

Bi_alloceffort_data[REVDATE] >= MIN ( Calendar[Date] )

&& Bi_alloceffort_data[REVDATE] <= MAX ( Calendar[Date] )

)

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

Anonymous — Mon, 07 Apr 2025 04:28:36 GMT

Hi ankurbajaj07 ,

Thanks for reaching out to the Microsoft Fabric Community.

The solution shared by techies is technically accurate. The DAX measure below correctly filters the Bi_alloceffort_data table to include only records where REVDATE falls within the selected date range from the calendar slicer:

Selected Manhours = CALCULATE ( SUM ( Bi_alloceffort_data[Manhours] ), FILTER ( Bi_alloceffort_data, Bi_alloceffort_data[REVDATE] >= MIN ( Calendar[Date] ) && Bi_alloceffort_data[REVDATE] <= MAX ( Calendar[Date] ) ) )

This approach ensures that only the relevant job instances and corresponding manhours within the selected date range are included in the calculation.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

Anonymous — Fri, 11 Apr 2025 00:52:16 GMT

Hi ankurbajaj07 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

Anonymous — Tue, 15 Apr 2025 01:06:43 GMT

Hi @ankurbajaj07 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Summing up of manhours based on slicer range selection corresponding to filtered jobs

Anonymous — Fri, 18 Apr 2025 02:25:34 GMT

Hi ankurbajaj07 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.