Measure to calculate occurrence of a character

[gautampruthi]

Hi,

I have a table named srs_cap with columns that contain decision information (cap_dec1, cap_dec2, cap_dec3) and the corresponding dates when those decisions were made (cap_decd, cap_dcd2, cap_dcd3).

• cap_dec1 corresponds to cap_decd,
• cap_dec2 corresponds to cap_dcd2,
• cap_dec3 corresponds to cap_dcd3.

The logic is as follows: Identify the most recent decision date from cap_decd, cap_dcd2, and cap_dcd3 that is on or before same date last year. If the corresponding decision (cap_dec1, cap_dec2, or cap_dec3) has the value 'C', then count the entries in cap_stuc.

i am using the below two measure one to check occurrence of C and second one to check U

Conditional_Count =

SUMX(

    srs_cap,

    VAR CutoffDate = DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))

    VAR Date1 = IF(AND([cap_decd] <= CutoffDate, NOT(ISBLANK([cap_decd]))), [cap_decd], BLANK())

    VAR Date2 = IF(AND([cap_dcd2] <= CutoffDate, NOT(ISBLANK([cap_dcd2]))), [cap_dcd2], BLANK())

    VAR Date3 = IF(AND([cap_dcd3] <= CutoffDate, NOT(ISBLANK([cap_dcd3]))), [cap_dcd3], BLANK())

    VAR MostRecentDate = MAXX(

        { Date1, Date2, Date3 },

        [Value]

    )

    VAR IsDecisionC = SWITCH(

        TRUE,

        MostRecentDate = Date1 && [cap_dec1] = "C", 1,

        MostRecentDate = Date2 && [cap_dec2] = "C", 1,

        MostRecentDate = Date3 && [cap_dec3] = "C", 1,

        0

    )

    RETURN

        IF(IsDecisionC = 1, 1, 0)

)

The same logic but now we are looking for U instead of C

UnConditional_Count =

SUMX(

    srs_cap,

    VAR CutoffDate = DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))

    VAR Date1 = IF(AND([cap_decd] <= CutoffDate, NOT(ISBLANK([cap_decd]))), [cap_decd], BLANK())

    VAR Date2 = IF(AND([cap_dcd2] <= CutoffDate, NOT(ISBLANK([cap_dcd2]))), [cap_dcd2], BLANK())

    VAR Date3 = IF(AND([cap_dcd3] <= CutoffDate, NOT(ISBLANK([cap_dcd3]))), [cap_dcd3], BLANK())

    VAR MostRecentDate = MAXX(

        { Date1, Date2, Date3 },

        [Value]

    )

    VAR IsDecisionU = SWITCH(

        TRUE,

        MostRecentDate = Date1 && [cap_dec1] = "U", 1,

        MostRecentDate = Date2 && [cap_dec2] = "U", 1,

        MostRecentDate = Date3 && [cap_dec3] = "U", 1,

        0

    )

    RETURN

        IF(IsDecisionU = 1, 1, 0)

)

But getting incorrect results, Sample data along with correct output and measure output is provided below, I am considering today data as cut off date (08/11/2023)

cap_stuc	cap_dec1	cap_decd	cap_dec2	cap_dcd2	cap_dec3	cap_dcd3	Correct output	Conditional_Count	UnConditional_Count
80171	C	01/08/2023 00:00	U	17/01/2024 00:00			Conditional	1	0
80335	C	28/11/2023 00:00	U	09/01/2024 00:00				1	1
103278								0	0
132353	C	14/06/2023 00:00					Conditional	1	0
160112	C	09/01/2024 00:00	U	18/01/2024 00:00				1	1
173991	C	01/08/2023 00:00	U	24/11/2023 00:00			Conditional	1	0
190815								0	0
191899	R	07/11/2023 00:00						0	0
191925	C	13/09/2023 00:00	U	17/01/2024 00:00			Conditional	1	0
190740	C	12/01/2023 00:00					Conditional	1	0
19088	R	13/09/2023 00:00						0	0
200052								0	0
200169								0	0
200269	C	21/09/2023 00:00	U	17/01/2024 00:00			Conditional	1	0
200284	C	11/05/2023 00:00					Conditional	1	0
200301	C	01/08/2023 00:00	U	18/12/2023 00:00			Conditional	1	0
200347	C	11/05/2023 00:00	U	17/11/2023 00:00			Conditional	1	0
210582	C	13/09/2023 00:00	U	14/09/2023 00:00			Unconditional	0	1
220366	R	04/08/2023 00:00						0	0
220371	C	14/06/2023 00:00	U	25/07/2023 00:00			Unconditional	0	1

[gautampruthi]

i managed to get the desired output by changing the measure as

Conditional_Count =

SUMX(

    srs_cap,

    VAR CutoffDate = DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))

    // Filter each date to ensure it's on or before the cutoff date

    VAR Date1 = IF([cap_decd] <= CutoffDate, [cap_decd], BLANK())

    VAR Date2 = IF([cap_dcd2] <= CutoffDate, [cap_dcd2], BLANK())

    VAR Date3 = IF([cap_dcd3] <= CutoffDate, [cap_dcd3], BLANK())

    // Find the latest date that is on or before the cutoff date

    VAR MostRecentDate = MAXX(

        FILTER(

            { Date1, Date2, Date3 },

            NOT(ISBLANK([Value]))

        ),

        [Value]

    )

    // Check if a valid MostRecentDate was found (i.e., it's not blank)

    VAR IsDecisionC = IF(

        NOT(ISBLANK(MostRecentDate)),

        SWITCH(

            TRUE,

            MostRecentDate = Date1 && [cap_dec1] = "C", 1,

            MostRecentDate = Date2 && [cap_dec2] = "C", 1,

            MostRecentDate = Date3 && [cap_dec3] = "C", 1,

            0

        ),

        0 // If MostRecentDate is blank, set IsDecisionC to 0

    )

    RETURN

        IF(IsDecisionC = 1, 1, 0)

)

[Anonymous]

Hi,@gautampruthi 
We are delighted that you have found a solution and are willing to share it.

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu

[gautampruthi]

Hi @Greg_Deckler Thank you for looking into this.
i have added more information along with the measure i am using and output i am getting.

[Greg_Deckler · Accepted solution]

@gautampruthi No idea where the 9 comes from. Likely, you should unpivot those three columns to make the calculation easier. Otherwise, you could use something like MC Aggregations: Multi-Column Aggregations (MC Aggregations) - Microsoft Fabric Community