Forum Discussion

Laagen's avatar
Laagen
Regular Visitor
3 years ago
Solved

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 mor...
Need Help
  • Adamboer's avatar
    Adamboer
    3 years ago

    To create a filter with 7 selections for the timespan labels you provided, you can follow these steps:

    1. Go to the data source you want to filter and select the column that contains the timespan labels.
    2. Click on the filter icon or right-click on the column and select "Filter".
    3. In the filter dialog box, select "Text Filters" or "Number Filters" depending on the type of data you have.
    4. Choose "Contains" or "Equals" as the filter condition, depending on your needs.
    5. Enter the label you want to filter on, for example, "Night1", "Morning1", etc.
    6. 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.

DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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 👍

  • Laagen's avatar
    Laagen
    Regular Visitor
    3 years ago

    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?

     

    • DOLEARY85's avatar
      DOLEARY85
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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.