topic Re: New to DAX, need help w/ COUNTROWS w/ a filter using multiple tables. in DAX Commands and Tips

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

rschubs — Mon, 18 Nov 2024 14:31:57 GMT

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.

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

lbendlin — Mon, 18 Nov 2024 22:14:03 GMT

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?

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

Laxmanjatoth — Tue, 19 Nov 2024 12:50:08 GMT

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

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

Anonymous — Wed, 27 Nov 2024 05:11:37 GMT

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.