Parameter scale slicer with multiple conditions

[jejnycta]

I have the following "Scale" parameter table with 2 columns:
[TABLE="class: cms_table_grid, width: 100"]
<tbody>[TR]
[TD]Overtime Hours Worked[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]<25[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]>=25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]>=50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]>=75[/TD]
[TD]75[/TD]
[/TR]
</tbody>[/TABLE]


Following is a sample "OT Data" table
[TABLE="class: cms_table_grid, width: 200, align: left"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]OT Hours Worked[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD]Mathew[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD]Joseph[/TD]
[TD="align: center"]90[/TD]
[/TR]
</tbody>[/TABLE]



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:

[TABLE="class: outer_border, width: 120, align: left"]
<tbody>[TR]
[TD="align: center"]Min[/TD]
[TD="align: center"]Max[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]49[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[TD="align: center"]74[/TD]
[/TR]
[TR]
[TD="align: center"]75[/TD]
[TD="align: center"]1000[/TD]
[/TR]
</tbody>[/TABLE]

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.