Create Data Flag using If condition

[Anonymous]

I have a data set where i have to Flag the customers as "Repeat" who are contacting support within 7 days. 

Below is a sample data of how the data is available in the database:

I need to build a Flag for the below scenarios to flag a customer as Repeat:

1: Same Day Repeat - if the customer_key and interaction_date is same but the interaction_id is different

2: 2-7 Day Repeat - if the customer_key is same but the interaction_id is different and the difference between the interaction date is between 2 -7

3: 7 Day Repeat - if the customer_key is same but the interaction_id is different and the difference between the interaction date is between 7

The data s sorted on the basis of Customer_key, and interaction date. I have calcualted the Repeat Customer Column in the below table in excel using the below logic : =IF(A2="NULL",0,IF(AND(A2=A1,C2=C1,B2<>B1),1,0))

customer_key	interection_id	interaction_date	Repeat Customer
000103335F7249C60A495C3F	5a241750-9e48-4760-8054-e8b492ee74a0	28-09-20	0
000103335F7249C60A495C3F	b4836c15-3570-4f28-ab05-ec04728f6d86	28-09-20	1
000C795C5ED8B5770A495EA6	361a66b9-b5b6-4cd8-9e10-061e069fe0a0	28-09-20	0
000C795C5ED8B5770A495EA6	398a9bcf-2e48-49d2-9596-71acefb43854	28-09-20	1
000C795C5ED8B5770A495EA6	6e4c5e85-fa67-4a57-8318-59421e3df7f2	28-09-20	1

[Anonymous · Accepted solution]

Hi @Anonymous 

Do you have all three column customer_key interection_id interaction_date the same values? Assume no...here is one way in M, paste in Advanced Editor of Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdE7bgMxDATQu2xtYimK39LZ2JcwXEha6v5HyMZwGQcxkH7wMEPebgsiFqy1ytWIY1M8c8hWr8tpkUZcTBAi2YFNERyFIb1zUKZxwyNGvmK**bleep**L/fQr2NmrjiJQxRB4kkPrKJAD2cin7q4/gpsdhFw+/UPMHuDl/J2sWppqD+jSFXjsfnQtCKglUWMmvmj4Egxv0ccEekyOnSAkFKy0kbNzdeG3QE0eki4wmxpwEwOvxUGCqWTdp01664Z/fUr8N+hrwSd4/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer_key = _t, interection_id = _t, interaction_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer_key", type text}, {"interection_id", type text}, {"interaction_date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_key"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [a=Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.AddColumn(a,"Repeated", each 
 if [Index]=0 then 0
 else if [interaction_date]=a{[Index]-1}[interaction_date] then 1 
 else if [interaction_date]-a{[Index]-1}[interaction_date]< #duration(7,0,0,0) then 2
 else 3 )][b]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"customer_key", "interection_id", "interaction_date", "Repeated"}, {"customer_key", "interection_id", "interaction_date", "Repeated"})
in
    #"Expanded Custom"

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien

[v-xulin-mstf · Accepted solution]

Hi @Anonymous 

You can try the following steps,

Step 1,create a column index base on base-table:

Step 2,create column   to sort customer_key by groups,

Column 2 =

RANKX(FILTER('Table','Table'[customer_key]=EARLIER('Table'[customer_key])),'Table'[Index],,ASC,Dense),then you will get the below:

Step 3,use the following measure:

repeat =

VAR test1 =

    CALCULATE (

        COUNT ( 'Table'[customer_key] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[customer_key] = MAX ( 'Table'[customer_key] )

        )

    )

VAR test2 =

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[interection_id] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[customer_key] = MAX ( 'Table'[customer_key] )

        )

    )

VAR test3 =

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[interaction_date] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[customer_key] = MAX ( 'Table'[customer_key] )

        )

    )

VAR TEST4 =

    CALCULATE (

        DATEDIFF (

            MIN ( 'Table'[interaction_date] ),

            MAX ( 'Table'[interaction_date] ),

            DAY

        ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[customer_key] = MAX ( 'Table'[customer_key] )

        )

    )

VAR a2 =

    MAX ( 'Table'[Column 2] )

VAR A1 =

    IF (

        a2 = 1,

        0,

        IF (

            test1 > 1

                && TEST3 = 1

                && TEST2 > 1,

            1,

            IF (

                TEST1 > 1

                    && TEST2 > 1

                    && TEST4 >= 2

                    && TEST4 < 7,

                2,

                IF ( TEST1 > 1 && TEST2 > 1 && TEST4 = 7, 3, BLANK () )

            )

        )

    )

RETURN

    A1

Final you will get what you want!

Wish it is helpful for you !

Click here to download pbix file if you need!

Best Regard

Lucien Wang

[Anonymous · Accepted solution]

Hi @Anonymous 

Do you have all three column customer_key interection_id interaction_date the same values? Assume no...here is one way in M, paste in Advanced Editor of Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdE7bgMxDATQu2xtYimK39LZ2JcwXEha6v5HyMZwGQcxkH7wMEPebgsiFqy1ytWIY1M8c8hWr8tpkUZcTBAi2YFNERyFIb1zUKZxwyNGvmK**bleep**L/fQr2NmrjiJQxRB4kkPrKJAD2cin7q4/gpsdhFw+/UPMHuDl/J2sWppqD+jSFXjsfnQtCKglUWMmvmj4Egxv0ccEekyOnSAkFKy0kbNzdeG3QE0eki4wmxpwEwOvxUGCqWTdp01664Z/fUr8N+hrwSd4/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer_key = _t, interection_id = _t, interaction_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer_key", type text}, {"interection_id", type text}, {"interaction_date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_key"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [a=Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.AddColumn(a,"Repeated", each 
 if [Index]=0 then 0
 else if [interaction_date]=a{[Index]-1}[interaction_date] then 1 
 else if [interaction_date]-a{[Index]-1}[interaction_date]< #duration(7,0,0,0) then 2
 else 3 )][b]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"customer_key", "interection_id", "interaction_date", "Repeated"}, {"customer_key", "interection_id", "interaction_date", "Repeated"})
in
    #"Expanded Custom"