Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
KDeRoo
Frequent Visitor

Counting records in Table 1 with filters in related Table 2

I have a table with donation records; we'll call that DonationTable.
Then I also have a table with donor records; we'll call that DonorTable.
The DonationTable and DonorTable are linked by the DonorID that is listed in both tables for each record. It's a 1 to many relationship (1 donor to many donations).
In the DonationTable I have a measure that calculates the total donations of each fiscal year (FY23 Donations Total and FY24 Donations Total).

 

What I'm trying to do is calculate the number of donors who gave this fiscal year and last fiscal year. So I'm trying to count the number of DonorIDs where the FY23 Donations Total and the FY24 Donations Total are greater than 0. 

 

Here is the problem I'm running into:

=CALCULATE(
    COUNTROWS(DonorTable),
       FILTER(DonationTable, [FY23 Donations Total] > 0 && [FY24 Donations Total] > 0)
)

When I add this measure to my pivot table, no values are entered in the column. 

 

The odd thing is, if I only use a sinlge filter on the COUNTROWS function, it works for either FY23 and FY24, but as soon as I try to look at both measures, it returns nothing. Even if I change the function to count the DonorIDs in the DonationsTable instead of counting rows in the DonorTable, the same thing happens... and I'm totally stumped as to where I'm going wrong.

 

 

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello @KDeRoo - the first thing to do would be to confirm in your data that there really are donors that have donations in both years.  If that has been confirmed then you can write the measure using the related distinct count DAX pattern like the example below.  Just be sure to update the logic so that it matches your actual FY logic.  This pattern creates two variables for donors that donated in each year respectively and then find the count of the intersection of the two.

 

# Donors with Donations in Both Years= 
VAR DonorsWithDonationsIn2023 =
    SUMMARIZE ( FILTER ( DonationsTable, YEAR(DonationsTable[DonationDate]) = 2023 ) , Donors[DonorId] )
VAR DonorsWithDonationsIn2024 =
    SUMMARIZE ( FILTER ( DonationsTable, YEAR(DonationsTable[DonationDate]) = 2024 ) ), Donors[DonorId] )
VAR DonorsWithDonationsInBothYears =
    INTERSECT ( DonorsWithDonationsIn2023, DonorsWithDonationsIn2024 )
VAR Result =
    COUNTROWS ( DonorsWithDonationsInBothYears )
RETURN
    Result

Read more about Dax Patterns here: https://www.daxpatterns.com/related-distinct-count/#:~:text=The%20Related%20distinct%20count%20patte... 

 

 

View solution in original post

2 REPLIES 2
KDeRoo
Frequent Visitor

You are my hero... this totally worked. I had to make a few small tweaks to make it work for our fiscal year, but that was easy because in the donations table I have a column that records which FY each donation falls into. 

 

I think where I went wrong was that I kept trying to use count and countrows functions and I totally forgot about the summarize function. 

 

Thank you so much!

jennratten
Super User
Super User

Hello @KDeRoo - the first thing to do would be to confirm in your data that there really are donors that have donations in both years.  If that has been confirmed then you can write the measure using the related distinct count DAX pattern like the example below.  Just be sure to update the logic so that it matches your actual FY logic.  This pattern creates two variables for donors that donated in each year respectively and then find the count of the intersection of the two.

 

# Donors with Donations in Both Years= 
VAR DonorsWithDonationsIn2023 =
    SUMMARIZE ( FILTER ( DonationsTable, YEAR(DonationsTable[DonationDate]) = 2023 ) , Donors[DonorId] )
VAR DonorsWithDonationsIn2024 =
    SUMMARIZE ( FILTER ( DonationsTable, YEAR(DonationsTable[DonationDate]) = 2024 ) ), Donors[DonorId] )
VAR DonorsWithDonationsInBothYears =
    INTERSECT ( DonorsWithDonationsIn2023, DonorsWithDonationsIn2024 )
VAR Result =
    COUNTROWS ( DonorsWithDonationsInBothYears )
RETURN
    Result

Read more about Dax Patterns here: https://www.daxpatterns.com/related-distinct-count/#:~:text=The%20Related%20distinct%20count%20patte... 

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors