Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |