Forum Discussion
Create a shift time filter
Hi!
Can someone help me, I´m trying to make a filter with 7 selections to only get data from the timespan with the right label.
i got Night1 23:00-03:00
Night2 00:00-03:00
Night3 03:00-06:00
morning1 06:00-11:00
morning2 11:00-15:00
Afternoon1 15:00-19:00
and Afternoon 2 19:00-23:59
Thanks
To create a filter with 7 selections for the timespan labels you provided, you can follow these steps:
- Go to the data source you want to filter and select the column that contains the timespan labels.
- Click on the filter icon or right-click on the column and select "Filter".
- In the filter dialog box, select "Text Filters" or "Number Filters" depending on the type of data you have.
- Choose "Contains" or "Equals" as the filter condition, depending on your needs.
- Enter the label you want to filter on, for example, "Night1", "Morning1", etc.
- Click on "OK" to apply the filter.
Repeat these steps for each label you want to filter on. This will allow you to get data only from the timespan with the right label.
If you want to adjust the filter selections, you can go back to the filter dialog box and modify the filter conditions or remove the ones you no longer need.
4 Replies
Hi,
this probably isn't the most elegant solution but you could create a calculated column in DAX with nested IF AND statements. You will have to take into account that where it goes over midnight the calculation doesn't work so you take the first part up to 23:59 and do the same from 00:00 - 03:00.
If done the first part for 23:00 - 03:00 below, just replace the else part with the next nested if's:
Column = IF(and(Time(Hour('Table (3)'[TimeField]),minute('Table (3)'[TimeField]),SECOND('Table (3)'[TimeField])) >= Time(23,00,00),Time(Hour('Table (3)'[TimeField]),minute('Table (3)'[TimeField]),SECOND('Table (3)'[TimeField])) <= Time(23,59,00)), "Night1", IF(and(Time(Hour('Table (3)'[TimeField]),minute('Table (3)'[TimeField]),SECOND('Table (3)'[TimeField])) >= Time(00,00,00),Time(Hour('Table (3)'[TimeField]),minute('Table (3)'[TimeField]),SECOND('Table (3)'[TimeField])) <= Time(03,00,00)),"Night1","Other"))If I answered your question, please mark my post as solution, Appreciate your Kudos 👍Thanks!
I dont know what im doing wrong but i cant find 'Table (3)'[TimeField] it only shows other Dax-Calculations, Any ide what im doing wrong?
Table 3 is your table and time field is your field within the table that contains the time.
If you're not seeing any of your data source fields check you are creating a calculated column and not a measure.
To create a filter with 7 selections for the timespan labels you provided, you can follow these steps:
- Go to the data source you want to filter and select the column that contains the timespan labels.
- Click on the filter icon or right-click on the column and select "Filter".
- In the filter dialog box, select "Text Filters" or "Number Filters" depending on the type of data you have.
- Choose "Contains" or "Equals" as the filter condition, depending on your needs.
- Enter the label you want to filter on, for example, "Night1", "Morning1", etc.
- Click on "OK" to apply the filter.
Repeat these steps for each label you want to filter on. This will allow you to get data only from the timespan with the right label.
If you want to adjust the filter selections, you can go back to the filter dialog box and modify the filter conditions or remove the ones you no longer need.
