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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Conditional queries in dax

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
2 ACCEPTED SOLUTIONS
Gabry
Super User
Super User

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

View solution in original post

Anonymous
Not applicable

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

 

vjtianmsft_0-1725867584082.png

 

vjtianmsft_1-1725867607286.png

 

vjtianmsft_2-1725867618265.png

 

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



View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

 

vjtianmsft_0-1725867584082.png

 

vjtianmsft_1-1725867607286.png

 

vjtianmsft_2-1725867618265.png

 

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



Anonymous
Not applicable

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
Super User
Super User

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

Anonymous
Not applicable

Yes I do

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.