Create a Measure for a Double-Distinct Count with a Filter on the Second Field

[ClackamasAshley]

I'm trying to create a measure to distinctly count the number of victims (M43[Person Name]) that occur in all the cases (M43[Case Number]) in my dataset. We have suspects in the data too, so (M43[Victim or Suspect]) needs to be filtered to just (M43[Victim or Suspect]) = "VICTIM_IN-OFFENSE" to only count the (M43[Person Name])'s who are victims.

The issue I'm having is the victim's name pops up in my dataset multiple times as the victim for every single crime (M43[Offense Name]) included in the case if there are more than one. I just want each victim to be counted once for each case, not 3 times if he/she is the victim of 3 different crimes in the same case. I'm also afraid that if I just DISTINCTCOUNT(M43[Person Name]) I will only count each victim once no matter how many cases they are the victim of. If the victim is a victim in multiple cases then I need he/she to be counted for each time he/she is a victim of a separate case (M43[Case Number]). 

In summary, I need a DAX formula that captures a distinct count for M43[Person Name] in each case M43[Case Number] if that person is a victim (M43[Victim or Suspect]) = "VICTIM_IN-OFFENSE", so a double-distinct-count with a filter on the second field. Hope that makes sense. Everything I'm trying that has worked in DAX captures the count as too high, as it's counting each victim in each case for each crime if there is multiple crimes included in the case. Thank you!

Ashley

[Anonymous]

Hi.

As I understand, you want to count 1 each for each person victim of a case.
If a person is victim of several crimes for one case, he shall only be counted 1 time for that case.

Right?

If so, edit you distinct count to count distinct of a concated string of
- victim name
- case number.
This can be done in several ways, for example by adding a calculated column in you dataset:

Thereafter, add the following measure for calculating the distinct number of person names + cases, filtered on Victim or suspect = "Victim" as follows: 

Distinct count of person name + cases = 
CALCULATE(
    DISTINCTCOUNT( M43[Person name and case]),
    M43[Victim or suspect]="Victim"
)

Hope this helps @ClackamasAshley 

Consider accepting as solution if it solves your problem.

Kind regards

[Greg_Deckler]

@ClackamasAshley Try something like this:

Measure = 
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        'M43',
        "__Person", [Person Name],
        "__Case", [Case Number]
      )
    )
  )

[VijayP]

@ClackamasAshley 

Create a merged column (in Power query) of Victim and Case (together) which will give you a distinct value and from there you can use the dax