Count measures when the sum of measure is greater than 1

[guanel1]

I created a measure to count how many studies a site currently has a status of ACTIVE.

Active Studies/Site = CALCULATE(COUNT('Status Site'[Status],FILTER('Status Site','Status Site'[Status]="Active")

-- This gave me 7 sites that had 2+ active studies. (i found this out by just counting the rows in the table with a measure of greater than 1)

I now want to create a CARD visual that will give a count the sites that have greater than 1 active study.

I cannot seem to create a measure to count these. Here is the calculation I tried.

Greater Than 1 Study = if([Active Studies/Site]>1,distinctcount('Status Site'[Site Name]),0)

--This calculation is giving me the count of all the rows, even the ones that have a zero in the row. There are 7 rows with a 1 in it and so i would assume that the total should be 7, not 97 (the total count of all rows).

I also tried to just pull in the Active Studies/Site measure into a CARD and create a filter to only count >1 but the filter would not let me filter on this measure in the CARD visual.

[v-cazheng-msft · Accepted solution]

Hi @guanel1 

You may try this Measure.

Greater Than 1 Study =

VAR _Table =

    SUMMARIZE (

        'Status Site',

        'Status Site'[Site Name],

        "Active Studies/Site", [Active Studies/Site]

    )

RETURN

    COUNTX (

        FILTER ( _Table, [Active Studies/Site] > 1 ),

        'Status Site'[Site Name]

    )

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[guanel1]

sorry for the messy view... here is the question again. 

I created a measure to count how many studies a site currently has a status of ACTIVE.

Active Studies/Site = CALCULATE(COUNT('Status Site'[Status],FILTER('Status Site','Status Site'[Status]="Active")
-- This gave me 7 sites that had 2+ active studies. (i found this out by just counting the rows in the table with a measure of greater than 1)

I also tried to just pull in the Active Studies/Site measure into a CARD and create a filter to only count >1 but the filter would not let me filter on this measure in the CARD visual.

I now want to create a CARD visual that will give a count the sites that have greater than 1 active study.

I cannot seem to create a measure to count these. Here is the calculation I tried.
Greater Than 1 Study = if([Active Studies/Site]>1,distinctcount('Status Site'[Site Name]),0)

--This calculation is giving me the count of all the rows, even the ones that have a zero in the row. There are 7 rows with a 1 in it and so i would assume that the total should be 7, not 97 (the total count of all rows).

[Whitewater100]

Hi:

Without seeing your data/model I can just suggest

Can you add in a calcuated column to your data table in first to count the active studies. IF({Active]), 1, BLANK())

Then a measure can sum it up with filter applied. If I can see the info the answer will be a bit better!

Thanks.