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'm am doing research to breakdown racial comparisons of victims to suspects/arrestees. This is a direct connection to a SQL database. For example, I can't figure out how to get a count of victim's that were white with a white suspect/arrestee. Then, I would need a count of how many victims that were white had a suspect/arrestee who were black, asian, hispanic, etc. Then do the same thing for the different victims that were black, asian, hispanic, etc. The rows will have the same "INC_ID_KEY". However, the way the columns are set up, if I filter for "Name_Type" of victim, I lose all of my "Subject_Types" in the count and vice versa. See attached of how it is showing from the direct query of the SQL database. For example (this is one incident below), I want to count how many asian victims had a white suspect, how do I do that with this below data?
Hi @ResearchKnell ,
First fill in the blanks of "Subject_Type" use below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMzMTMwUNJRCi5NykpNLgGywnXDPTxDXEFiocEBrs4hQJZSrA6S2rDM5JLMXCDDUdcx2NPRTz/A0dnTzdNZwd/Z1dEPpFxHydPPxTPM0yXU0QdVr3N+bkFOYmZeYl4JfgOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INC_ID_Key = _t, Name_Type = _t, Race = _t, Subject_Type = _t, Victim_Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"INC_ID_Key", Int64.Type}, {"Name_Type", type text}, {"Race", type text}, {"Subject_Type", type text}, {"Victim_Type", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Max(Table.SelectRows(Source,each [INC_ID_Key]=[INC_ID_Key]),"Subject_Type")),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Subject_Type"}, {"Custom.Subject_Type"})
in
#"Expanded Custom"
And you will see:
Then you could filter the rows and get the counts.
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
What I'm trying to do is count how many Inc_ID_Keys (that are the same number) have a white suspect and an asian female (for example from the data listed above as if there were mulitple records like the one shown above but each unique record is connected by that Inc_ID_Key).
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.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |