Parameter scale slicer with multiple conditions

[jejnycta]

I have the following "Scale" parameter table with 2 columns:

Overtime Hours Worked	Value
<25	24
>=25	25
>=50	50
>=75	75

<tbody>
</tbody>

Following is a sample "OT Data" table

Employee	OT Hours Worked
John	30
Mark	60
Luke	70
Mathew	80
Joseph	90

<tbody>
</tbody>

This is my attempt to create a DAX formula that when a slicer is selected such as >=75, the pivot table should show 2 instances (Mathew & Joseph) with total:170 OT Hours Worked:

ScaledOTWorked:=IF(HASONEVALUE(Scale[Value]),.......................... I only got this far. Please help

Thanks!!!!

 

[citizenbh]

Hi jejnycta,

1. Delete column [Value] (not necessary), and add new two columns:

Min	Max
0	24
25	49
50	74
75	1000

<tbody>
</tbody>

2. In table 'OT_Data' add new column [Group] with next formula:

=CALCULATE(VALUES(Scale[Overtime Hours Worked]),FILTER(Scale,[OT Hours Worked]>=Scale[Min] && [OT Hours Worked]<=Scale[Max]))

3. Create PivotTable (use OT_Data)
- Row: Employee

4. Slicer: Group

 

[jejnycta]

Thank you but I posted my question incorrectly. I apologize. When selecting the slicer I wanted a count of how many people for example worked equal to or greater than 30 hours - the answer I should get is 5 when I select the slicer >=25. Another example, when I select slicer >=50, I should get a count of 4 (Mark, Luke, Mathew and Joseph).

I strongly believe that the "HASONEVALUE" function has to come into play.