Forum Discussion

ResearchKnell's avatar
ResearchKnell
Frequent Visitor
5 years ago

Direct Query - Two counts in the same visual with different filters

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?  

 

 

 

 

Need Help

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!

    • ResearchKnell's avatar
      ResearchKnell
      Frequent Visitor
      5 years ago

      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).