Unexpected results when summarizing with a Flag measure

[WBscooby]

Hi

Please can someone help. This problem is driving me nuts. I have created a measure to calculate the maximum intervention ID for each ChildviewID from FACT_InterventionETEAttendance. I cannot do this using a calculated column as the results will vary depending on the date range e.g. the results may be by quarter or by year.

I have tested the elements of the main measure as individual measures and the flag seems to correctly identify the correct result. However, when I attempt to summarize using the flag in a separate measure, the results are unexpected. See example below for ID 875496. The correct flag is identified but it summarizes as 2 distinct CV_INTIDs. In this example, I know there are 2 CV_INTIDS in the same year with differing levels of seriousness and end date.

Please see DAX code below for measures. Any help would be much appreciated. Thank you

Flag_MaxInt =

-- Store Childview ID into a variable
VAR ChildviewID = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID])

-- Calculate the Minimum Seriousness for the given Childview ID
VAR MaxSeriousness =
CALCULATE(
MIN(FACT_Intervention_ETEAttendance[Seriousness]),
FILTER(
ALLSELECTED(FACT_Intervention_ETEAttendance),
FACT_Intervention_ETEAttendance[CHILDVIEWID] = ChildviewID &&
FACT_Intervention_ETEAttendance[END_DATE] IN VALUES(FACT_Intervention_ETEAttendance[END_DATE])
)
)

-- Calculate the Maximum End Date for the given Childview ID and Max Seriousness
VAR MaxEndDate =

CALCULATE(
MAX(FACT_Intervention_ETEAttendance[END_DATE]),
FILTER(
ALLSELECTED(FACT_Intervention_ETEAttendance),
FACT_Intervention_ETEAttendance[CHILDVIEWID] = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID]) &&
FACT_Intervention_ETEAttendance[Seriousness] = MaxSeriousness)
)

-- Calculate the Maximum Intervention ID for the given Childview ID, Max Seriousness, and Max End Date
VAR MaxIntervention =

CALCULATE(
MAX(FACT_Intervention_ETEAttendance[CV_INTID]),
FILTER(
ALLSELECTED(FACT_Intervention_ETEAttendance),
FACT_Intervention_ETEAttendance[CHILDVIEWID] = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID]) &&
FACT_Intervention_ETEAttendance[Seriousness] = MaxSeriousness &&
FACT_Intervention_ETEAttendance[END_DATE] = MaxEndDate &&
FACT_Intervention_ETEAttendance[END_DATE] IN VALUES(FACT_Intervention_ETEAttendance[END_DATE])
)
)

-- Flag the row if it matches the Max Intervention ID

VAR Flag =

CALCULATE (
MAX ( FACT_Intervention_ETEAttendance[CV_INTID]),
FILTER ( ALLSELECTED( FACT_Intervention_ETEAttendance), FACT_Intervention_ETEAttendance[CV_INTID]=MaxIntervention ))
RETURN
IF ( Flag <> BLANK (), 1, 0 )


Sum_FlagMaxInt = CALCULATE(
DISTINCTCOUNT(FACT_Intervention_ETEAttendance[CV_INTID]),
FILTER(FACT_Intervention_ETEAttendance,
[FlagMeasure]=1
)
)

[WBscooby · Accepted solution]

After another 48 hours, I managed to solve this. Final dax below and was able to sumx the results:

FlagTest =

VAR ChildviewID = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID])

VAR MaxSeriousness =

    CALCULATE(

        MIN(FACT_Intervention_ETEAttendance[Seriousness]),

        ALLSELECTED(FACT_Intervention_ETEAttendance),

        KEEPFILTERS(FACT_Intervention_ETEAttendance[END_DATE]),

        FACT_Intervention_ETEAttendance[CHILDVIEWID] = ChildviewID

    )

VAR MaxEndDate =

CALCULATE(

    MAX(FACT_Intervention_ETEAttendance[END_DATE]),

    FILTER(

        ALLSELECTED(FACT_Intervention_ETEAttendance),

        FACT_Intervention_ETEAttendance[CHILDVIEWID] = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID]) &&

        FACT_Intervention_ETEAttendance[Seriousness] = MaxSeriousness)

    )

VAR MaxIntervention =

CALCULATE(

    MAX(FACT_Intervention_ETEAttendance[CV_INTID]),

    FILTER(

        ALLSELECTED(FACT_Intervention_ETEAttendance),

        FACT_Intervention_ETEAttendance[CHILDVIEWID] = MAX(FACT_Intervention_ETEAttendance[CHILDVIEWID]) &&

        FACT_Intervention_ETEAttendance[Seriousness] = MaxSeriousness &&

        FACT_Intervention_ETEAttendance[END_DATE] = MaxEndDate &&

        FACT_Intervention_ETEAttendance[END_DATE] IN VALUES(FACT_Intervention_ETEAttendance[END_DATE])

    )

)

VAR SelectedIntervention = SELECTEDVALUE(FACT_Intervention_ETEAttendance[CV_INTID])

VAR Flag = IF(SelectedIntervention = MaxIntervention, 1, 0)

RETURN

    IF(

        HASONEVALUE(FACT_Intervention_ETEAttendance[CV_INTID]),

        Flag,

        BLANK()

    )

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...