Filter a measure to remove duplicates from another column

[SophRow]

Hi,

I've created the following measure:

Total Queries on AE Form = CALCULATE(COUNTROWS('Query Metrics'),'Query Metrics'[Visit]="Adverse Event") + 0

And what I want to do is filter it by a different column ('Participant') so that I remove any duplicates i.e., all i want to know if the % of unique participants in a trial who have a query on the AE form.

 This is a snip of the data - in this example I'd only want a count of 1 for participant 022-004 if i was interested in queries on the Screening A1 form for example.

Thanks!

Soph

[FreemanZ]

hi @SophRow 

are you expecting a table with the Participant columnn and a measure like this:

Percent = 

VAR TotalAE = 

CALCULATE(

    COUNTROWS('Query Metrics'),

    'Query Metrics'[Visit]="Adverse Event"

) + 0

VAR AllAE = 

CALCULATE(

    COUNTROWS('Query Metrics'),

    FILTER(

        ALL('Query Metrics'),

        'Query Metrics'[Visit]="Adverse Event"

    )

)  + 0

RETURN

DIVIDE(TotalAE, AllAE)