Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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.
@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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |