Most Efficient Way to Count with Filters from Another Table

[Txtcher]

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])

[FBergamaschi · Accepted solution]

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

[Txtcher]

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.  

[techies · Accepted solution]

Hi @Txtcher please try this

Allegation =

CALCULATE(

    COUNTROWS(Allegations),

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

)

[FBergamaschi · Accepted solution]

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