Forum Discussion
DAX formula
Hi KavithaN,
Thank you for reaching out to the Microsoft fabric community forum.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Regards,
Community Support Team.
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