IF, AND , COUNT ROWS and RELATEDTABLE Query Help needed

[Anonymous]

Beginner at PBI

Added a column with below query to get Employee Status

Status = if(and(COUNTROWS(RELATEDTABLE('Master Data'))>=1,COUNTROWS(RELATEDTABLE('Collated LB'))>=1),COUNTROWS(RELATEDTABLE('Completers Table'))>=1),"Completer", if(and(COUNTROWS(RELATEDTABLE('Master Data'))>=1,COUNTROWS(RELATEDTABLE('Collated LB'))>=1),COUNTROWS(RELATEDTABLE('Completers Table'))>=0),"Enrolled","Yet to Start"))

Not Sure why this does not work. 😞

Please advice  

[Siriwat_Lukin]

Hi @Anonymous,

Could you please share example you data and result that you want?

Thank you,

[ryan_mayu]

@Anonymous 

AND can only has two conditions. if you have three conditions, it needs to be  and(and(condition1, condition2),condition3)

pls try to use && 

[Anonymous]

Hi @ryan_mayu 

Can you write it in a query form and show as an example or edit below query

Status = if(and(COUNTROWS(RELATEDTABLE('Master Data'))>=1,COUNTROWS(RELATEDTABLE('Collated LB'))>=1),COUNTROWS(RELATEDTABLE('Completers Table'))>=1),"Completer", if(and(COUNTROWS(RELATEDTABLE('Master Data'))>=1,COUNTROWS(RELATEDTABLE('Collated LB'))>=1),COUNTROWS(RELATEDTABLE('Completers Table'))>=0),"Enrolled","Yet to Start"))

[ryan_mayu · Accepted solution]

@Anonymous 

change and to &&, pls have a try.

Status = if(COUNTROWS(RELATEDTABLE('Master Data'))>=1 && COUNTROWS(RELATEDTABLE('Collated LB'))>=1) && COUNTROWS(RELATEDTABLE('Completers Table'))>=1),"Completer", if(COUNTROWS(RELATEDTABLE('Master Data'))>=1 && COUNTROWS(RELATEDTABLE('Collated LB'))>=1) && COUNTROWS(RELATEDTABLE('Completers Table'))>=0),"Enrolled","Yet to Start"))

[Anonymous]

WOWW it worked 😃

Little change with parenthesis. Below is the updated one

Status = if(COUNTROWS(RELATEDTABLE('Master Data'))>=1 && COUNTROWS(RELATEDTABLE('Collated LB'))>=1 && COUNTROWS(RELATEDTABLE('Completers Table'))>=1,"Completer", if(COUNTROWS(RELATEDTABLE('Master Data'))>=1 && COUNTROWS(RELATEDTABLE('Collated LB'))>=1 && COUNTROWS(RELATEDTABLE('Completers Table'))>=0,"Enrolled","Yet to Start"))