topic Re: Create a measure or column to show if all 3 fees have been paid in DAX Commands and Tips

Create a measure or column to show if all 3 fees have been paid

harsin — Mon, 11 Dec 2023 04:05:47 GMT

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.

Re: Create a measure or column to show if all 3 fees have been paid

Jihwan_Kim — Mon, 11 Dec 2023 04:27:25 GMT

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" )

Re: Create a measure or column to show if all 3 fees have been paid

harsin — Mon, 11 Dec 2023 05:14:10 GMT

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

Re: Create a measure or column to show if all 3 fees have been paid

Jihwan_Kim — Mon, 11 Dec 2023 06:03:40 GMT

Hi,

please share your sample pbix file's link, and then I can try to look into it.

Re: Create a measure or column to show if all 3 fees have been paid

ThxAlot — Mon, 11 Dec 2023 08:25:42 GMT

Flag = COUNTROWS( WINDOW( 1, ABS, 0, REL, ALLSELECTED( PYMT[Candidate], PYMT[week] ), ORDERBY( PYMT[week] ), PARTITIONBY( PYMT[Candidate] ) ) ) = 3