Forum Discussion
Helper VMost Efficient Way to Count with Filters from Another Table
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])
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 threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
3 Replies
- FBergamaschi
Super User
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 threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
TxtcherThank you FBergamaschi & techies . Both solutions work. 😁
And, the measure I created was not working because it was counting intakes, not allegations. Doh!
Good grief.