Help Counting Number of Records Filtered in Two Separate Tables

[DarrelDonatto]

I need to count the number of times that both a column in one table has a certain value and a column in another table has a certain value.

I have a Filter for the 'epro trauma data' table: 

CALCULATE(COUNTROWS('epro trauma data'), FILTER('epro trauma data', 'epro trauma data'[TIME_OF_TRAUMA_ALERT]<>""))

I have a filter for the 'cad inmain' table: 

CALCULATE(COUNTROWS('cad inmain'), FILTER('cad inmain','cad inmain'[secsar2tr]<900))

What I need is to count the rows in 'epro trauma data' when both conditions exist.

'epro trauma data' is linked to 'epro incident data' by a one-to-one relationship with filtering in both directions.

'epro incident data' is linked to 'cad inmain' by a many-to-one relationship with filtering in both directions.

Any help would be appreciated.

Once this is all done - I need to be able to use slicers for date and a few other fields in "'epro incident data'

[v-yanjiang-msft · Accepted solution]

Hi @DarrelDonatto ,

According to your description, here's my solution.

Count is a measure in epro trauma data table.

Count = 
CALCULATE (
    COUNTROWS ( 'epro trauma data' ),
    FILTER (
        ALL ( 'epro trauma data' ),
        'epro trauma data'[TIME_OF_TRAUMA_ALERT] <> BLANK ()
    ),
    FILTER ( ALL ( 'cad inmain' ), 'cad inmain'[secsar2tr] < 900 )
)

 Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DarrelDonatto]

Thank you so much.  Works perfectly..

[lbendlin]

You can combine these filters

CALCULATE(COUNTROWS('epro trauma data'), 'epro trauma data'[TIME_OF_TRAUMA_ALERT]<>"",'cad inmain'[secsar2tr]<900)

However your data model may not support this query.