Distinct Count with multiple criterias

[michael_knight]

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'

Membership	Interview Date	Mandatory	Worry Date	Result
111	02/12/2021	No	15/11/2021	TRUE
111	25/11/2021	No	15/11/2021	TRUE
111	23/11/2021	Yes	15/11/2021	TRUE
111	15/11/2021	No	15/11/2021	TRUE
111	09/11/2021	No
111	02/11/2021	No
111	29/10/2021	No
111	22/10/2021	No
111	15/10/2021	No
111	07/10/2021	No
111	30/09/2021	No
222	23/09/2021	No	23/08/2021	FALSE
222	17/09/2021	No	23/08/2021	FALSE
222	06/09/2021	No	23/08/2021	FALSE
222	23/08/2021	No	23/08/2021	FALSE
222	23/08/2021	No	23/08/2021	FALSE
222	18/08/2021
222	09/08/2021
222	29/07/2021
222	19/07/2021
222	12/07/2021
222	09/07/2021
222	05/07/2021
222	02/07/2021

Does anyone know how I can do this?

Thanks,

Mike

[v-eqin-msft · Accepted solution]

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.

[amitchandak]

@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())