Forum Discussion

lee_the_mee's avatar
lee_the_mee
New Member
3 years ago
Solved

New Column with COUNTROWS with filter to exclude multiple values

Hi All,

I have the report that queries the Active directory data to fetch the member's details of some specific security groups in the Power BI report.
I want to exclude three groups values from the "top.cn" column while comparing the cell values in the same column using "Earlier" and populating the values in a new column but whenever I tried to use the "OR" (||) condition then I get the below error on my machine having configuration -  Processor i7 & 32 GB RAM.

The query I'm trying to use to achieve this.

AD_Multiple Group Membership = COUNTROWS(FILTER(MyDomain,
(MyDomain[top.cn]<>"Group A" || MyDomain[top.cn]<>"Group B" || MyDomain[top.cn]<>"Group C" && MyDomain[group.member.cn]) = EARLIER(MyDomain[group.member.cn]))

 

The below existing query for creating the new column which excludes "Group A" only works perfectly fine.

AD_Multiple Group Membership = COUNTROWS(FILTER(MyDomain,
(MyDomain[top.cn]<>"Group A" && MyDomain[group.member.cn]) = EARLIER(MyDomain[group.member.cn]))

 

Can someone guide me, on how I can achieve this?

Thanks in Advance 🙂 

Need Help
  • lee_the_mee's avatar
    lee_the_mee
    3 years ago

    Hi lbendlin,
    It didn't work but thanks a lot for the tip, I'm able to resolve it with the below query.

     

    AD_Multiple group Membership = COUNTROWS(FILTER(MyDomain, EARLIER(MyDomain[group.member.cn]) = MyDomain[group.member.cn] && MyDomain[top.cn]<> "Group A" && MyDomain[top.cn]<> "Group B" && MyDomain[top.cn]<> "Group C"))

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You need to protect your OR statements, otherwise AND takes precedence.

     

    Having said that, your OR statements will not actually do anything

     

    Try this instead

     

    AD_Multiple Group Membership = 
COUNTROWS(FILTER(MyDomain,
MyDomain[top.cn] NOT IN {"Group A","Group B","Group C"} && MyDomain[group.member.cn] = EARLIER(MyDomain[group.member.cn]))
    • lee_the_mee's avatar
      lee_the_mee
      New Member
      3 years ago

      Hi lbendlin,
      It didn't work but thanks a lot for the tip, I'm able to resolve it with the below query.

       

      AD_Multiple group Membership = COUNTROWS(FILTER(MyDomain, EARLIER(MyDomain[group.member.cn]) = MyDomain[group.member.cn] && MyDomain[top.cn]<> "Group A" && MyDomain[top.cn]<> "Group B" && MyDomain[top.cn]<> "Group C"))