Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bikgx
Frequent Visitor

Count Row with multi condition and multi cut off time

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=10007... 

 

what i expected is; 

IF

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

IF

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

 

ClientDate Createdpicking_state
AAA11/15/2022 20:10waiting
AAA11/15/2022 11:09waiting
AAA11/15/2022 10:06waiting
AAA11/15/2022 15:16waiting
AAA11/14/2022 19:56waiting
AAA11/14/2022 15:11waiting
AAA11/14/2022 19:56waiting
AAA11/14/2022 18:11waiting
   
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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:

EylesIT_1-1668499944615.png

 

 

View solution in original post

2 REPLIES 2
bikgx
Frequent Visitor

HI @Anonymous 

 

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

 

danke

Anonymous
Not applicable

@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:

EylesIT_1-1668499944615.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors