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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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