Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.