How to find hours range

Super User

3 years ago

Hi Anonymous ,

We are bit unclear with your table structure where you capture the Hours data, so we have considered below table which holds hours value,

Also, the Slicer to filter these hours, we have created a table with options you mentioned as shown in below screenshot.

In order to filter the data based on selection you need to create a Measure in Data table with DAX expression as:

Filter Hours = SWITCH(SELECTEDVALUE(Hours[Hours]), "<=3 hrs", IF(SELECTEDVALUE(Data[Data Hours])<=3,"In","Out") ,

"3-8 hrs",IF(SELECTEDVALUE(Data[Data Hours])>3 && SELECTEDVALUE(Data[Data Hours])<8,"In","Out")

,"8-12 hrs",IF(SELECTEDVALUE(Data[Data Hours])>=8 && SELECTEDVALUE(Data[Data Hours])<12,"In","Out"),

"12-24 hrs",IF(SELECTEDVALUE(Data[Data Hours])>12 && SELECTEDVALUE(Data[Data Hours])<=24,"In","Out"),

">24 hrs",IF(SELECTEDVALUE(Data[Data Hours])>24,"In","Out")

This will check the selected value in the slicer i.e. filter and based on current value of table row validate the hours falls within selected range or out of the range.

Please refer to the below screenshot for the same.

Now in order to filter the table for respective data to be displayed in table as per the range selection we need to add filter to the table as Filter Hours = In as shown in the below screenshot.