Forum Discussion

michael_knight's avatar
michael_knight
Post Prodigy
4 years ago
Solved

Distinct Count with multiple criterias

Hi,

 

I am trying to replicate the following below in Excel into Power BI. Basically I need to do an ALLEXCEPT of some sort with the Membership, check if the Worry Date is not blank and then if on any row the Mandatory is set to 'Yes' then return 'TRUE'

 

MembershipInterview DateMandatoryWorry DateResult
11102/12/2021No15/11/2021TRUE
11125/11/2021No15/11/2021TRUE
11123/11/2021Yes15/11/2021TRUE
11115/11/2021No15/11/2021TRUE
11109/11/2021No  
11102/11/2021No  
11129/10/2021No  
11122/10/2021No  
11115/10/2021No  
11107/10/2021No  
11130/09/2021No  
22223/09/2021No23/08/2021FALSE
22217/09/2021No23/08/2021FALSE
22206/09/2021No23/08/2021FALSE
22223/08/2021No23/08/2021FALSE
22223/08/2021No23/08/2021FALSE
22218/08/2021   
22209/08/2021   
22229/07/2021   
22219/07/2021   
22212/07/2021   
22209/07/2021   
22205/07/2021   
22202/07/2021   

 

Does anyone know how I can do this?

 

Thanks,

Mike

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  michael_knight ,

     

    Please try:

    Result = 
var _t=SUMMARIZE(FILTER('Table',[Membership]=EARLIER('Table'[Membership])&&  [Worry Date]<>BLANK()  &&[Worry Date]=EARLIER('Table'[Worry Date])),[Mandatory])
return  IF("Yes" in _t, TRUE(),FALSE())

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    michael_knight , Try a new column like

     

    new column =
    var _cnt = countX(filter(Table, not(isblank([Worry Date])) && [Membership] = earlier([Membership]) && [Mandatory] ="Yes"),[Membership])
    return
    if(isblank(_cnt), false(), True())

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  michael_knight ,

     

    Please try:

    Result = 
var _t=SUMMARIZE(FILTER('Table',[Membership]=EARLIER('Table'[Membership])&&  [Worry Date]<>BLANK()  &&[Worry Date]=EARLIER('Table'[Worry Date])),[Mandatory])
return  IF("Yes" in _t, TRUE(),FALSE())

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.