DAX formula

Hi KavithaN 

  There are a couple of issues here.

1. You're using X[AssessDate] <= MaxDate but AssessDate has an inactive relationship to Dim_Time. So MAX(Dim_Time[Date]) won't automatically filter X. You need to use 'USERELATIONSHIP' to activate the relationship.

2. You want to filter 2 assignments, but you're currently using [EventCount] >= 1, this should be 2

 Here's the revised DAX:

VAR MaxDate = MAX(Dim_Time[Date])

VAR IDswithTwoAssessments =
    FILTER(
        ADDCOLUMNS(
            VALUES(X[RID]),
            "EventCount",
            CALCULATE(
                DISTINCTCOUNT(X[IDEvent]),
                REMOVEFILTERS(Dim_Time),
                USERELATIONSHIP(X[AssessDate], Dim_Time[Date]),
                X[AssessDate] <= MaxDate
            )
        ),
        [EventCount] >= 2
    )

VAR Result =
    CALCULATE(
        DISTINCTCOUNT(Y[RID]),
        REMOVEFILTERS(Dim_Time),
        Y[RDate] <= MaxDate,
        ISBLANK(Y[DDate]) || Y[DDate] > MaxDate,
        TREATAS(
            SELECTCOLUMNS(IDswithTwoAssessments, "RID", [RID]),
            Y[RID]
        )
    )

RETURN Result

I hope this helps, please mark as solved if it does!

kudos always welcome .

Connect with me on LinkedIn

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Thank you no it doesnt help I have tried enabling the realtionship but it didn't work

VAR MaxDate = MAX(Dim_Time[Date])

-- Step 1: Get RIDs with at least one assignment before or on MaxDate
VAR ValidRIDs =
CALCULATETABLE(
VALUES(X[RID]),
FILTER(
X,
X[AssessDate] <= MaxDate
)
)

-- Step 2: Count RIDs from Y who are active as of MaxDate and exist in ValidRIDs
VAR ActiveRIDs =
CALCULATE(
DISTINCTCOUNT(Y[RID]),
FILTER(
Y,
Y[RDate] <= MaxDate &&
(ISBLANK(Y[DDate]) || Y[DDate] > MaxDate)
),
TREATAS(ValidRIDs, Y[RID]),
REMOVEFILTERS(Dim_Time)
)

RETURN ActiveRIDs

The code is complex so it is difficult to reply with somw insights without some dat ato play with and a clear view of what you want to obtain, so:

please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here