New to DAX, need help w/ COUNTROWS w/ a filter using multiple tables.

[rschubs]

Hello everyone,

I am attempting to create a calculated column in the 'WFS Metrics' table with the following formula. My goal is to count the rows in the 'BAT Counts' table where the 'BAT Counts'[Full Name] field matches a name in the 'WFS Metrics'[Full Name] table/field while also making sure that the 'ACS BAT'[Condition] field equals "DCS-START" while none of the three tables have an active relationship. Any help would be appreciated, thanks.

[Anonymous · Accepted solution]

Thanks for the replies from Laxmanjatoth and lbendlin.

Hi @rschubs ,

It is recommended that you create relationships between the three table. The RELATED function can then be used:

Column = COUNTROWS(FILTER('BAT Counts',RELATED('ACS BAT'[Condition])="DCS-START"&&'BAT Counts'[Full Name]=RELATED('WFS Metrics'[Full Name]))
)

 The LOOKUPVALUE function can be used without creating a relationship, for example:

condition = LOOKUPVALUE('ACS BAT'[Condition],'ACS BAT'[Index],'BAT Counts'[Index])

Column = COUNTROWS(FILTER('BAT Counts','BAT Counts'[Full Name]=RELATED('WFS Metrics'[Full Name])&&'BAT Counts'[condition]="DCS-START"))

Best Regards,
Zhu

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

[Laxmanjatoth]

Matching Count =
CALCULATE(
COUNTROWS('BAT Counts'),
FILTER(
'BAT Counts',
'BAT Counts'[Full Name] = 'WFS Metrics'[Full Name] &&
'ACS BAT'[Condition] = "DCS-START"
)
)

let me know if you still help 

Best,
Laxman jatoth 

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?  How are these three tables related?