topic Count Row with multi condition and multi cut off time in DAX Commands and Tips

Count Row with multi condition and multi cut off time

bikgx — Tue, 15 Nov 2022 07:53:34 GMT

Hi all;

i am a beginner but tried to figured out my issue by my self and search on this community but still confused, i have dataset ( attached here), https://docs.google.com/spreadsheets/d/1QCzmcZA4p8jHXXPBKLVbqpfgZGbU2mGO/edit?usp=sharing&ouid=100079262241875009342&rtpof=true&sd=true

what i expected is;

CLIENTt ='AAA', Count picking state='waiting" where Date Created between TODAYDATE(-1) > HOUR 16:30 to TODAYDATE <= HOUR 16:30

CLIENT = 'BBB ', Count picking state= 'waiting' where Date Created between TODAYDATE(-1) > HOUR 13:30 to TODAYDATE <= HOUR 13:30

ELSE

Count picking state = 'waiting' where Date Created between TODAYDATE(-1) > HOUR 15.00 to TODAYDATE <= HOUR 15.00

note IF TODAY DATE is 11/15/2022 and TODAYDATE(-1) is 11/14/2022 (YESTERDAY)

Result for CLIENT AAA = "6" waiting job

what makes me confused is condition count in Interval Time (multi cut off time) , i tried to get Hour and Minute first and compared it, but it didnt worked

Thank you for your help;

Regards;

Azura

Client	Date Created	picking_state
AAA	11/15/2022 20:10	waiting
AAA	11/15/2022 11:09	waiting
AAA	11/15/2022 10:06	waiting
AAA	11/15/2022 15:16	waiting
AAA	11/14/2022 19:56	waiting
AAA	11/14/2022 15:11	waiting
AAA	11/14/2022 19:56	waiting
AAA	11/14/2022 18:11	waiting

Re: Count Row with multi condition and multi cut off time

Anonymous — Tue, 15 Nov 2022 08:12:36 GMT

bikgx , please try the following measure:

WaitingJobCount = SUMX( 'Table1', VAR vTime = SWITCH('Table1'[Client] ,"AAA", TIME(16, 30, 0) ,"BBB", TIME(13, 30, 0) ,TIME(15, 0, 0) ) VAR vStartTime = (TODAY()-1) + vTime VAR vEndTime = TODAY() + vTime VAR vJobWaitingCount = IF( 'Table1'[picking_state] = "waiting" && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime , 1 , 0 ) RETURN vJobWaitingCount )

This give me these results below:

Re: Count Row with multi condition and multi cut off time

bikgx — Tue, 22 Nov 2022 01:50:55 GMT

HI Anonymous

Thank you for your help, i got your idea and get the result what i expected,

danke