Identify duplicate ID to determine repeat user

[gsarma]

I am trying to identify the distinct count of devices where the app was launched twice on a given day

I have a table like below. In the example below

Distinct device count where the app was re-launched = 1 for 5/7 and 0 for all dates

Any help is appreciated.

date     device action
7-May d1        applaunch
7-May d1        applaunch
8-May d2        applaunch
8-May d1        appcrash
8-May d2        appclose
8-May d3        applaunch
9-May d1        applaunch

[AlB · Accepted solution]

Hi @gsarma 

Try this, were Table1 is the table you show:

1. Place Table1[Date] in the rows of a table visual

2. Place this measure in the visual

Measure =
SUMX (
    DISTINCT ( Table1[Device] ),
    IF (
        CALCULATE ( COUNT ( Table1[Action] ), Table1[Action] = "applaunch" ) > 1,
        1,
        0
    )
)

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers 

[Anonymous · Accepted solution]

Hi @gsarma 

Same in M/Power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3MNU3MjAyUNJRSjEEEokFBTmJpXnJGUqxOgSlLZCljfBLQ3UnFyUW49acnJNfnIopa4xhtCUeh8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, device = _t, action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"device", type text}, {"action", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "device", "action"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([action] = "applaunch") and [Count] > 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

Kind regards,

JB

[Anonymous · Accepted solution]

Hi @gsarma 

Same in M/Power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3MNU3MjAyUNJRSjEEEokFBTmJpXnJGUqxOgSlLZCljfBLQ3UnFyUW49acnJNfnIopa4xhtCUeh8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, device = _t, action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"device", type text}, {"action", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "device", "action"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([action] = "applaunch") and [Count] > 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

Kind regards,

JB

[AlB · Accepted solution]

Hi @gsarma 

Try this, were Table1 is the table you show:

1. Place Table1[Date] in the rows of a table visual

2. Place this measure in the visual

Measure =
SUMX (
    DISTINCT ( Table1[Device] ),
    IF (
        CALCULATE ( COUNT ( Table1[Action] ), Table1[Action] = "applaunch" ) > 1,
        1,
        0
    )
)

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers 

[gsarma]

How do I do this if I dont need the date on the visualization. For example, I am adding two KPI cards - one for count of users and one for count of repeat users. How do we get that

[dax · Accepted solution]

Hi @gsarma , 

You could try below M code and refer to my sample for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3MNU3MjAyUNJRSjEEEokFBTmJpXnJGUqxOgSlLZCljfBLQ3UnFyUW49acnJNfnIopa4xhtCUeh8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, device = _t, action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"device", type text}, {"action", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "device", "action"}, {{"all", each _, type table [date=date, device=text, action=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index", 0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"index"}, {"Custom.index"})
in
    #"Expanded Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.