Counting (frequency) and avoiding double counting due to LEFT JOIN

[rjusa]

I have a dataset that represents testing cases for chemicals and I want to count the number of positive tests for each chemical.

case_id	chemical_identified	case_type
24-0001	benzene	A
24-0001	benzene	B
24-0001	toluene	A
24-0001	toluene	B
24-0002	benzene	A
24-0002	toluene	A
24-0003	phenol	C

There is a field I'll call "case_type" and case_id to case_type is one-to-many.    Due to the left join when bringing in the case_type table, counting chemicals will double count those records where there is more than one case_type.  I do have a slicer visual and the count work when I'm slicing on a single case_type, but of course breaks if I select multiples, or none at all.

My workaround is a new column where I concatenated case_id with chemical_identified, and then in my visual, I distinctcount that column for the true number of positive results.  

case_id	chemical_identified	case_type	case_id_chemical
24-0001	benzene	A	24-0001benzene
24-0001	benzene	B	24-0001benzene
24-0001	toluene	A	24-0001toluene
24-0001	toluene	B	24-0001toluene
24-0002	benzene	A	24-0002benzene
24-0002	toluene	A	24-0002toluene
24-0003	phenol	C	24-0003phenol

My question is if this pattern is addressed better with a DAX function.  I attempted to create a temporary table to remove or ignore the case_type column but never figured it out and gave up since the new column was working.  I didn't want to pivot it out to new columns as I didn't know how to deal with that with my slicer visual.

Sorry for the squished table...the website is telling me it is invalid HTML when I try to set the width to something like 500px.

[danextian · Accepted solution]

Hi @rjusa 

You can address that with a measure containing a tempory table of summarize case_id and chemical identifier and then counting the rows of the summary.

Case count = 
COUNTROWS (
    SUMMARIZE (
        'chemicals',
        'chemicals'[case_id],
        'chemicals'[chemical_identified]
    )
)

You can see in the screenshot below that there is a count for each case type row but at the chemical identifier level, the count is just one. This essentially  returns a unique combination of case_id and chemical_identified like you did with the calc column and then counting how many rows those are.