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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GKJARC
Resolver I
Resolver I

Flag multiple calls within a number of consecutive days

Hi All,

 

The aim is to flag when a customer calls by telephone three times or more within 5 days.
Here's an example of the desired outcome, the first 4 columns are already in the dataset:

GKJARC_2-1662541505025.png

 

The rows for each customer are already grouped with an index number.
I created a simplified PBIX-file, however since I'm not a super user it seems not possible to upload it here. The data model is this:

GKJARC_3-1662542270248.png

 

Hopefully this is sufficient info, otherwise please let me know so I can clarify.

Thanks!

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @GKJARC ,

 

According to your description, it seems that the values of flag(Index 1 , 2 , 3) should be 1. If I misunderstood, please clearify the real logic.

1.png

Or if your logic is flag when a customer calls by telephone three times or more within 5 days, please try:

 

Flag = 
VAR _a =
    COUNTROWS (
        FILTER (
            'Table',
            [Customer name] = EARLIER ( 'Table'[Customer name] )
                && [Call date]
                    >= EARLIER ( 'Table'[Call date] ) - 5
                && [Call date]
                    <= EARLIER ( 'Table'[Call date] ) + 5
        )
    )
RETURN
    IF ( _a >= 3, 1, 0 )

 

Final output:

vjianbolimsft_0-1662605851639.png

 

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @GKJARC ,

 

According to your description, it seems that the values of flag(Index 1 , 2 , 3) should be 1. If I misunderstood, please clearify the real logic.

1.png

Or if your logic is flag when a customer calls by telephone three times or more within 5 days, please try:

 

Flag = 
VAR _a =
    COUNTROWS (
        FILTER (
            'Table',
            [Customer name] = EARLIER ( 'Table'[Customer name] )
                && [Call date]
                    >= EARLIER ( 'Table'[Call date] ) - 5
                && [Call date]
                    <= EARLIER ( 'Table'[Call date] ) + 5
        )
    )
RETURN
    IF ( _a >= 3, 1, 0 )

 

Final output:

vjianbolimsft_0-1662605851639.png

 

You're right, the first 3 rows in the example data should also be flagged.
Thanks for your solution!

GKJARC
Resolver I
Resolver I

Table in text format:

 

IndexCustomer nameCall date DateInteger
1Joe08-05-2022 08:25:00  20220508
2Joe08-05-2022 10:30:00  20220508
3Joe08-05-2022 13:00:00  20220508
4Joe16-05-2022 14:30:00  20220516
5Joe16-05-2022 15:00:00  20220516
6Joe17-05-2022 14:30:00  20220517
7Joe18-05-2022 15:00:00  20220518
8Joe20-05-2022 15:20:00  20220520
9Joe02-08-2022 10:12:00  20220802
10Joe10-08-2022 10:12:00  20220810
11Joe17-08-2022 10:12:00  20220817
12Joe19-08-2022 10:12:00  20220819
1Peter04-05-2022 09:05:00  20220504
2Peter20-05-2022 11:00:00  20220520
3Peter22-05-2022 09:00:00  20220522
4Peter23-05-2022 09:05:00  20220523
5Peter06-09-2022 09:05:00  20220906
6Peter06-09-2022 16:00:00  20220906

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.