# Order & # NO Order by time range condition

Hi ADSL ,

I suggest you to create a [Time Group] column in your VISIT_SUMM table.

Time Group = 
IF (
    VISIT_SUMM[TIME_IN] = BLANK ()
        || VISIT_SUMM[TIME_OUT] = BLANK (),
    BLANK (),
    SWITCH (
        TRUE (),
        VISIT_SUMM[TIME_IN] >= TIME ( 1, 0, 0 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 7, 59, 59 ), "Early Morning (1:00:00 am - 7:59:59 am)",
        VISIT_SUMM[TIME_IN] >= TIME ( 8, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 11, 59, 59 ), "Morning (8:00:00 am - 11:59:59 am)",
        VISIT_SUMM[TIME_IN] >= TIME ( 12, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 13, 29, 59 ), "Lunch (12:00:00 pm - 13:29:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 13, 30, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 17, 29, 59 ), "Afternoon (13:30:00 pm - 17:29:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 17, 30, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 20, 59, 59 ), "Evening (17:30:00 pm - 20:59:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 21, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 23, 59, 59 ), "Late evening (21:00:00 pm - 23:59:59 pm)"
    )
)

Then create a calculated table.

DimTimeGourp =
DATATABLE (
    "TimeGroup", STRING,
    "GroupSort", INTEGER,
    "TimeIn", STRING,
    "TimeOut", STRING,
    {
        { "Early Morning (1:00:00 am - 7:59:59 am)", 1, "1:00:00 am", "7:59:59 am" },
        { "Morning (8:00:00 am - 11:59:59 am)", 2, "8:00:00 am", "11:59:59 am" },
        { "Lunch (12:00:00 pm - 13:29:59 pm)", 3, "12:00:00 pm", "13:29:59 pm" },
        { "Afternoon (13:30:00 pm - 17:29:59 pm)", 4, "13:30:00 pm", "17:29:59 pm" },
        { "Evening (17:30:00 pm - 20:59:59 pm)", 5, "17:30:00 pm", "20:59:59 pm" },
        { "Late evening (21:00:00 pm - 23:59:59 pm)", 6, "21:00:00 pm", "23:59:59 pm" }
    }
)

Data model:

Measures:

#Order = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[ORDER_IND] = "E" && VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

NO Order = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[ORDER_IND] = BLANK() && VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

Customer Visited = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

Result is as below.

Best Regards,
Rico Zhou

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