Calculate co-infections

[brwalker]

Hello,

I'm trying to calculate the number of co-infections in a dataset (sample below).  Sample 1 tests for 5 pathogens; sample 2 only tests for 2.  I want to flag if one sample has multiple pathogens and count the number of co-infections by sample and total number.  

Thanks in advance! 

Sample ID, Event, Result, Co-Infection, Result Measure
1, Pathogen 1, Positive, Flag, 1,
1, Pathogen 2, Positive, Flag,
1, Pathogen 3, Positive, Flag,
1, Pathogen 4, Negative,
1, Pathogen 5, Negative,
2, Pathogen 1, Negative, 0,
2, Pathogen 2, Negative,

[brwalker]

I apologize @PaulDBrown. For the first sample, there are 3 positives and two negatives; the second sample has no positives.  So sample 1 would be flagged with co-infections; sample 2 zero co-infections with a total of three positives between the two samples. 

[PaulDBrown · Accepted solution]

See if this works:

Co-infections =
VAR _CI =
    CALCULATE (
        DISTINCTCOUNT ( fTable[Event] ),
        FILTER ( ALLEXCEPT ( fTable, fTable[Sample ID] ), fTable[Result] = "Positive" )
    )
RETURN
    IF ( AND ( _CI > 1, MAX ( fTable[Result] ) = "Positive" ), "Co-infection" )

Co-infections by sample = 
COUNTROWS(FILTER(fTable, [Co-infections] = "Co-infection"))

Sample PBIX file attached

[brwalker]

Very close, @PaulDBrown .  Other than a matrix table, is it possible to aggregate Co-infections, and display, for instance, two cards: 2 co-infections & 5 by sample? 

Thank you so much! 

[PaulDBrown]

Not sure what you mean. Can you provide a depiction?

[PaulDBrown]

Sorry I'm not too sure what you are after. Can you expand a bit?