Forum Discussion

ClackamasAshley's avatar
ClackamasAshley
Frequent Visitor
3 years ago

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

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[Vic...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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