Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have a table of candidates who have been placed. A fee is charged once they have been in the role for 4 weeks, for 12 weeks and then at 22 weeks.
For example:
Candidate | Date | week |
A | 01/02/23 | 4 |
B | 01/03/23 | 4 |
C | 11/03/23 | 4 |
D | 12/03/23 | 4 |
E | 25/03/23 | 4 |
A | 01/03/23 | 12 |
C | 11/04/23 | 12 |
D | 12/03/23 | 12 |
A | 05/06/23 | 22 |
I need to find a way to create a measure or column to show if all 3 fees (4,12,22) have been paid, such as the below:
Candidate | Date | week | All 3 weeks? |
A | 01/02/23 | 4 | No |
B | 01/03/23 | 4 | No |
C | 11/03/23 | 4 | No |
D | 12/03/23 | 4 | No |
E | 25/03/23 | 4 | No |
A | 01/03/23 | 12 | No |
C | 11/04/23 | 12 | No |
D | 12/03/23 | 12 | No |
A | 05/06/23 | 22 | Yes |
My initial thought was to check if the candidate had a fee for all 3 week marks within a 22 week period but thought the way above would be best.
Flag =
COUNTROWS(
WINDOW(
1, ABS,
0, REL,
ALLSELECTED( PYMT[Candidate], PYMT[week] ),
ORDERBY( PYMT[week] ),
PARTITIONBY( PYMT[Candidate] )
)
) = 3
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
Hi,
I am not sure if I understood your question correctly, but please try something like below if it suits your requirement.
Please check the below picture and the attached pbix file.
expected result CC =
VAR _t =
SUMMARIZE (
FILTER (
Data,
Data[Candidate] = EARLIER ( Data[Candidate] )
&& Data[Date] <= EARLIER ( Data[Date] )
),
Data[week]
)
VAR _conditionone = { 4 } IN _t
VAR _conditiontwo = { 12 } IN _t
VAR _conditionthree = { 22 } IN _t
RETURN
IF ( _conditionone && _conditiontwo && _conditionthree, "Yes", "No" )
Thank you Jihwan_Kim, I got the below error:
Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values. So I changed your code slightly:
expected result CC =
VAR _t =
SUMMARIZE (
FILTER (
'WFA Revenue',
'WFA Revenue'[PARTICIPANT_ID] = EARLIER ( 'WFA Revenue'[PARTICIPANT_ID] )
&& 'WFA Revenue'[PAYMENT_DATE] <= EARLIER ( 'WFA Revenue'[PAYMENT_DATE] )
),
'WFA Revenue'[Outcome Week]
)
VAR _conditionone = { "4" } IN _t
VAR _conditiontwo = { "12" } IN _t
VAR _conditionthree = { "22" } IN _t
RETURN
IF ( _conditionone && _conditiontwo && _conditionthree, "Yes", "No" )
However all results are no, there should be some yes
Hi,
please share your sample pbix file's link, and then I can try to look into it.