DAX formula

[KavithaN]

Hi Folks,

I have a requirement where I have to find count of people who have completed atleast one assignmnets as of monthend I have defined the dax like below but for some reason IDswithTwoAssessments 

is returning very less records than expected and hence when I am trying to filter table Y using the IDs from IDswithTwoAssessments results in less numbers than expected.

Table X and Y are related by RID column 

Table X has inactive relationship to Dim_time by [AssessDate]

Table Y has active relationship to Dim_time by [RefDate] and inactive relationship to dim_time by [DisDate]

I am using this dax in a bar chart with Monthyear as my xaxis so every month MaxDate will hold the MonthendDate

----------------------------------------------------------------------------------------

below DAX is now working as expected and need help or other ways to achive?

-----------------------------------------------------------------------------------------

VAR MaxDate = MAX(Dim_Time[Date]) 

VAR IDswithTwoAssessments =

    FILTER(

        ADDCOLUMNS(

            VALUES(X[RID]),

            "EventCount",

            CALCULATE(

                DISTINCTCOUNT(X[IDEvent]),

                REMOVEFILTERS(Dim_Time),

                X[AssessDate] <= MaxDate 

            )

        ),

        [EventCount] >= 1

    )

VAR X = 

    CALCULATE(        

        DISTINCTCOUNT(Y[RID]),

            Y[RDate] <= MaxDate,

            (ISBLANK(Y[DDate]) || Y[DDate] > MaxDate),          

            REMOVEFILTERS(Dim_Time),                        

             // Filter: Only rids with at least 2 assignmnets before or on MaxDate

           TREATAS(

            SELECTCOLUMNS(IDswithTwoAssessments, "RID", [RID]),

            Y[RID]

        )

    )

RETURN

  X

I tried achieveing the same using the DAX below but this is taking more time and visual rendering is very slow can I get help in fixing the above dax ? or any other ways to achieve this? 

-------------------------------------------------------

below DAX is working as expected but very slow:

-------------------------------------------------------
VAR MaxDate = MAX(Dim_Time[Date])
RETURN
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( Y[RID] ),
FILTER (
ALLEXCEPT (
Y,
Dim_H[Area]
),
Y[RDate] <= MaxDate
&& (Y[DDate] > MaxDate || Y[DDate] = BLANK ())
&& CALCULATE (DISTINCTCOUNT ( X[IDEvent] ),X[AssessDate] <=MaxDate) >= 1),
REMOVEFILTERS ( Dim_Time[Date] )
)
)

[v-hjannapu · Accepted solution]

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.      

[v-hjannapu]

Hi @KavithaN,
Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.

If you have any further questions, feel free to reach out anytime.

Regards,
Harshitha.

[v-hjannapu]

Hi @KavithaN,
Hope everything’s working fine now! If you’ve found a fix, feel free to share it . It really helps others in the community. If the issue persists, please provide a sample dataset so we can assist you with a solution. Don’t hesitate to reach out if you need further help.

Regards,
Community Support Team.
 

[FBergamaschi]

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

[Shahid12523]

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

[KavithaN]

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

[wardy912]

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!