Conditional queries in dax

[Circuscazz]

Hi I have two tables, one with parent id and another with guardian ids. I want to do a look up when when the parent id equals the guardian id then call it 'primary' otherwise 'secondary'. In both tables the ID can be duplicated. I then want to count the number of primary and secondary by day. Can someone help please? Date Parent ID 01/01/2024 123 02/01/2024 123 03/01/2024 124 04/01/2024 126 05/01/2024 127 Date Guardian ID 02/01/2024 123 03/01/2024 124 04/01/2024 124 05/01/2024 126 06/01/2024 127

[Gabry · Accepted solution]

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

[v-jtian-msft · Accepted solution]

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

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Circuscazz .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

[Circuscazz]

Hi, to elaborate, i'm looking for every occurance where each cell of the parent id is shown in the column guardian id. I would like to label the cell next to the parent id as 'primary'. I then want to count all the cell with the term Primary.  This is needed for every row in the parent id column

[Gabry · Accepted solution]

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

[Circuscazz]

Yes I do