Filter by checkin

[Anonymous]

What i want is the users that made more than 1 checkIn in the same day. 

Is that posssible? thanks 🙂 
This will be better with a new table 

Original data:

user	place	date
1	A	01/01/2020 11:53
1	B	01/01/2020 13:33
1	C	03/01/2020 00:53
2	A	01/01/2020 09:53
2	C	01/01/2020 10:30
3	A	01/01/2020 11:53
3	B	02/01/2020 11:53
3	C	03/01/2020 11:53
3	D	04/01/2020 11:53

Result: 

user	place	date
1	A	01/01/2020 11:53
1	B	01/01/2020 13:33
2	A	01/01/2020 09:53
2	C	01/01/2020 10:30

[v-rzhou-msft · Accepted solution]

Hello @LuisELopez

You can test my way of using dax to create a new calculated table.

I build a table like yours, to get tested.

Calculated table:

Table2 = 
VAR _T1 =
    ADDCOLUMNS ( Table1, "Date2", FORMAT ( Table1[date], "dd/mm/yyyy" ) )
VAR _T2 =
    ADDCOLUMNS (
        _T1,
        "Count1",
            COUNTAX (
                FILTER ( _T1, [user] = EARLIER ( [user] ) && [Date2] = EARLIER ( [Date2] ) ),
                [Date2]
            )
    )
RETURN
    SUMMARIZE ( FILTER ( _T2, [Count1] > 1 ), [user], [place], [date] )

Result:

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Anonymous , Create a solution both as a column, that you can use.

As a measure used in visual.

Please find the file after the signature

[Fowmy]

@Anonymous 

If you want to it in power query, follow these 3 steps

Add Column > Custom Column: 

[user] &"|"& Text.From(DateTime.Date([date]))

Go to Home Tab and click keep rows > Keep Duplicates

Full Code that you can paste in a blank query and check the steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi1OLVLSUSrISUxOBdIpiSWpSrE60UqGQI4jEBsY6gORkYGRgYKhoZWpMVzSCU3S2MoYIekMkjSGSxoYwHQaYRprYIks6YxmrIGVsQFY0hifg4xhDjLCIYnmIFRJF5CkCbpkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type datetime}}, "en-gb"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [user] &"|"& Text.From(DateTime.Date([date]))),
    #"Kept Duplicates" = let columnNames = {"Custom"}, addCount = Table.Group(#"Added Custom", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Added Custom", columnNames, removeCount, columnNames, JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Kept Duplicates",{"Custom"})
in
    #"Removed Columns"

Let me know if you need in DAX

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn