topic Re: DAX Measure for Ranking the valid rows and calculating the average rating in DAX Commands and Tips

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

SRHR — Wed, 29 Apr 2026 03:29:30 GMT

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

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

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

Juan-Power-bi — Wed, 29 Apr 2026 17:07:19 GMT

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])

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

krishnakanth240 — Fri, 01 May 2026 02:02:07 GMT

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])

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

pcoley — Sun, 03 May 2026 03:37:33 GMT

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.

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

v-prasare — Mon, 04 May 2026 11:25:31 GMT

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

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

v-prasare — Thu, 07 May 2026 11:06:40 GMT

Hi @SRHR,

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