topic Most Efficient Way to Count with Filters from Another Table in DAX Commands and Tips

Most Efficient Way to Count with Filters from Another Table

Txtcher — Tue, 15 Jul 2025 18:42:45 GMT

I have 2 tables: HCR Intakes & Allegations. The are related to each other with a 1-many relationship using a field called Intake Id (HCR Intakes have many Allegations).

I am trying to obtain a count of the allegations for each Intake in HCR Intakes.

This is the measure I created and it is working, but I am still new to DAX and am wondering if this is an efficient way:

Allegation Count = VAR xFilterAllegations = FILTER(Allegations,Allegations[Intake Id] = RELATED('HCR Intakes'[Intake Id])) RETURN CALCULATE( COUNTROWS(RELATEDTABLE('HCR Intakes')), xFilterAllegations)

Here are some sample tables:

HCR Intakes:

Intake Id	Date Recieved
1	1/1/2025
2	1/20/2025
3	3/5/2025
4	4/25/2025

Allegations

Allegation No	Intake Id
A1	1
A2	1
A3	2
A4	2
A5	5
A6	6
A7	7

Expected outcome for Count of Allegations is: 4 (Intake Id 1 & 2 match HCR Intake [Intake id])

Re: Most Efficient Way to Count with Filters from Another Table

FBergamaschi — Tue, 15 Jul 2025 18:52:33 GMT

Hi,

I would reduce te measure to only

Allegation Count NEW =

CALCULATE(

COUNTROWS( Allegations ),

'HCR Intakes'

)

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Most Efficient Way to Count with Filters from Another Table

techies — Tue, 15 Jul 2025 19:06:19 GMT

Hi Txtcher please try this

Allegation =

CALCULATE(

COUNTROWS(Allegations),

TREATAS(VALUES('HCR Intakes'[Intake Id]), Allegations[Intake Id])

)

Re: Most Efficient Way to Count with Filters from Another Table

Txtcher — Tue, 15 Jul 2025 20:29:24 GMT

Thank you FBergamaschi & techies . Both solutions work. 😁

And, the measure I created was not working because it was counting intakes, not allegations. Doh!
Good grief.