Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
@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())
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |