DAX Measure for Ranking the valid rows and calculating the average rating

[SRHR]

Hi,

Appreciate any help.

I have two tables – Attendance, Survey I don’t have any joining keys other than email and no way of determining to which survey they responded to if the same MS forms are used for multiple learnings.

Attendance

Email	Course Name	Learning Date	Status	Form Key
Eve@gmail.com	Mathematics - 1	15/03/2026	Completed	Form 1
Eve@gmail.com	Mathematics - 2	20/04/2026	Completed	Form 1
Eve@gmail.com	Mathematics - 3	29/04/2026	Completed	Form 2
Adam@gmail.com	Mathematics - 1	20/04/2026	Registered	Form 1
Jack@gmail.com	Mathematics - 1	20/04/2026	Completed	Form 1
Jack@gmail.com	Mathematics - 3	29/04/2026	Completed	Form 2

Survey

Email	Form Key	Survey Date	Rating
Eve@gmail.com	Form 1	19/03/2026	5.0
Eve@gmail.com	Form 1	19/04/2026	2.0
Eve@gmail.com	Form 1	22/04/2026	3.8
Eve@gmail.com	Form 2	29/04/2026	2.9
Jack@gmail.com	Form 1	20/04/2026	4.0
Adam@gmail.com	Form 1	21/04/2026	3.5
Jack@gmail.com	Form 2	28/04/2026	2.6
Jack@gmail.com	Form 2	30/04/2026	4.6

• Adam has registered for the learning(20/04/2026) but didn’t attend. But he was able to submit a survey with an automated link that was sent to them. This becomes an invalid submission
• Eve attended the learning(20/04/2026), submitted a survey before the session(through an automated link – invalid submission) and after the event.
• Jack attended the learning(20/04/2026) and submitted the survey.

Now I need,

• a rank measure to rank the rows(where valid rows have rank =1). One assumption I can make to rank them is that Learning date <= surveydate<= Learning date + 2 days.
• a measure which calculates average rating of the valid responses

Ex

• Mathematics – 1, learning date(15/03/2026) = average = blank(no submissions)
• Mathematics – 1, learning date(20/04/2026) = average = 4.0
• Mathematics – 2, learning date(20/04/2026) = average = 3.8
• Mathematics – 3, learning date(29/04/2026) = average = (2.9+4.6)/2 = 3.75

[krishnakanth240 · Accepted solution]

Hi @SRHR 

Can you try these

Rank Valid Measure =

VAR LearningDate = SELECTEDVALUE(Attendance[Learning Date])

VAR Email = SELECTEDVALUE(Attendance[Email])

VAR FormKey = SELECTEDVALUE(Attendance[Form Key])

RETURN

IF(SELECTEDVALUE(Attendance[Status]) = "Completed" &&CALCULATE(

COUNTROWS(Survey),

FILTER(

            Survey,

            Survey[Email] = Email &&

            Survey[Form Key] = FormKey &&

            Survey[Survey Date] >= LearningDate &&

            Survey[Survey Date] <= LearningDate + 2

        ) ) > 0,1,0)

Avg Rating Measure =

AVERAGEX(FILTER(

Survey,

VAR MatchLearningDate =

CALCULATE(MAX(Attendance[Learning Date]),

FILTER(

                    Attendance,

                    Attendance[Email] = Survey[Email] &&

                    Attendance[Form Key] = Survey[Form Key] && Attendance[Status] = "Completed"))

RETURN

NOT ISBLANK(MatchLearningDate) && Survey[Survey Date] >= MatchLearningDate &&

Survey[Survey Date] <= MatchLearningDate + 2),

Survey[Rating])

[v-prasare]

Hi @SRHR,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @SRHR,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@pcoley , @krishnakanth240 & @Juan-Power-bi ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[pcoley · Accepted solution]

I understand the challenge of linking survey responses without a clear identifier.

Valid Survey Rank =
CALCULATE (
    COUNTROWS ( Survey ),
    FILTER (
        Survey,
        Survey[Email]
            = EARLIER ( Attendance[Email] )
            && Survey[Form Key]
                = EARLIER ( Attendance[Form Key] )
            && Survey[Survey Date] > Attendance[Learning Date]
    )
)

 This formula counts survey responses where the date is after the learning date for the same email and form.

[krishnakanth240 · Accepted solution]

Hi @SRHR 

Can you try these

Rank Valid Measure =

VAR LearningDate = SELECTEDVALUE(Attendance[Learning Date])

VAR Email = SELECTEDVALUE(Attendance[Email])

VAR FormKey = SELECTEDVALUE(Attendance[Form Key])

RETURN

IF(SELECTEDVALUE(Attendance[Status]) = "Completed" &&CALCULATE(

COUNTROWS(Survey),

FILTER(

            Survey,

            Survey[Email] = Email &&

            Survey[Form Key] = FormKey &&

            Survey[Survey Date] >= LearningDate &&

            Survey[Survey Date] <= LearningDate + 2

        ) ) > 0,1,0)

Avg Rating Measure =

AVERAGEX(FILTER(

Survey,

VAR MatchLearningDate =

CALCULATE(MAX(Attendance[Learning Date]),

FILTER(

                    Attendance,

                    Attendance[Email] = Survey[Email] &&

                    Attendance[Form Key] = Survey[Form Key] && Attendance[Status] = "Completed"))

RETURN

NOT ISBLANK(MatchLearningDate) && Survey[Survey Date] >= MatchLearningDate &&

Survey[Survey Date] <= MatchLearningDate + 2),

Survey[Rating])

[Juan-Power-bi]

 Hola

the best  approach is to do this in Power Query rather than DAX, create a custom column in Attendance that looks up matching survey rows by Email + Form Key within the date window. But if you want to keep it in DAX, here's an average rating measure:
daxAvg Valid Rating =
VAR LearningDate = MAX(Attendance[Learning Date])
VAR FormKey = MAX(Attendance[Form Key])

VAR ValidSurveys =
FILTER(
Survey,
Survey[Form Key] = FormKey
&& Survey[Survey Date] >= LearningDate
&& Survey[Survey Date] <= LearningDate + 2
&& Survey[Email] IN
CALCULATETABLE(
VALUES(Attendance[Email]),
Attendance[Status] = "Completed",
Attendance[Learning Date] = LearningDate,
Attendance[Form Key] = FormKey
)
)

RETURN
AVERAGEX(ValidSurveys, Survey[Rating])