Conditional queries in dax

Hello,

I don't really understand what do you mean with "when the parent id equals the guardian id", do you mean for every specific day?

Take a look on this pbix

Hello,Gabry ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,Anonymous .I am glad to help you.

Thanks to Gabry's help, calculate column [P-S ] works very well to achieve the following effect:
Find the corresponding Parent in the Parent table based on the date, and then determine if this 'Parent' is equal to the 'Guardian' in the 'Guardian' table, thus determining the type of 'Guardian'

I have added an extra twist to this for your needs.
For each row, calculate the number of [P-S]=“Primary” and the number of [P-S]=“Secondary”.
like this:
I used the filter function to redefine the calculation environment in the calculate function, and used the ALL function to remove the external filters in the table 'Guardian' and calculate only the corresponding quantities.

count_Primary = 
CALCULATE (
    COUNT ( 'Guardian'[P - S] ),
    FILTER ( ALL ( Guardian ), 'Guardian'[P - S] = "Primary" )
)

count_Secondary = 
CALCULATE (
    COUNT ( 'Guardian'[P - S] ),
    FILTER ( ALL ( Guardian ), 'Guardian'[P - S] = "Secondary" )
)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian