Forum Discussion

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous,

     

    as it is not possible to send you the data I will make pictures and tables: 
    Modell is the following:

    All Tickets Table: 
    Is merge of closed and opened Tickets and connected to other table via tickets

    All Orders Table:
    Is merge of closed an opened orders and connected to other table via orders
    Date Table:

    Normal Date Table with dates etc.

     

    Closed Orders Table:

    Start of orderEnd of orderOrder CodeTicket CodeEquipmentMachine FailureFailure Time
    27.06.202322.07.2023100087400001No0
    25.12.202328.12.2023100118400021Yes12
    10.11.202318.12.2023100219400111Yes0
    10.08.202320.08.20231003310400334No0
    01.01.202301.01.20231003511400245Yes8
    23.12.202329.12.2023100128400021Yes12
    01.07.202315.07.2023100157400001No0

     

    Open Orders Table:

    Start of orderOrder CodeTicket CodeEquipmentMachine FailureFailure Time
    01.03.2024100011400001No0
    05.01.2024100022400022Yes5
    10.01.2024100053400102No0
    15.01.2024100074400304Yes10
    03.01.2024100202400022Yes5

     

    Closed Tickets Table:

    Start of malfunctionEnd of malfunctionTicket CodeOrder CodeEquipmentMachine FailureFailure Time
    25.06.202328.07.2023710008400001No0
    22.12.202330.12.2023810011400021Yes12
    10.11.202320.11.2023910021400111Yes0
    08.08.202310.08.20231010033400334No0
    01.01.202402.01.20241110035400245Yes8


    Open Tickets Table:

    Start of malfunction

    		Ticket CodeOrder CodeEquipmentMachine FailureFailure Time
    01.01.2024110001400001No0
    02.01.2024210002400022Yes5
    10.01.2024310005400102No0
    12.01.2024410007400304Yes10
    15.01.2024510010400256Yes7
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      We can create a date table as a slicer. Note that this date table does not have any modeling relationship with the other tables.

       

       

      Date Table = CALENDAR(Date(2023,1,1),Date(2024,12,31))

       

      We can create measures.

       

      Flag = IF( MAX('Closed Orders Table'[Start of order]) >= MIN('Date Table'[Date]) && MAX('Closed Orders Table'[Start of order]) <= MAX('Date Table'[Date]),1,0)
      Flag2 = IF( MAX('Open Orders Table'[Start of order]) >= MIN('Date Table'[Date]) && MAX('Open Orders Table'[Start of order]) <= MAX('Date Table'[Date]),1,0)
      Flag3 = IF( MAX('Closed Tickets Table'[Start of malfunction]) >= MIN('Date Table'[Date]) && MAX('Closed Tickets Table'[Start of malfunction]) <= MAX('Date Table'[Date]),1,0)
      Flag4 = IF( MAX('Open Tickets Table'[Start of malfunction]) >= MIN('Date Table'[Date]) && MAX('Open Tickets Table'[Start of malfunction]) <= MAX('Date Table'[Date]),1,0)

       

      Place [Flag=1] on visual object screening .

       

      If this doesn't help, give your expected results.

       

      Best Regards,

      Neeko Tang

      If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.